CODEWITHSUNDEEP
ms-accessms-access-2007
jerry
jerry

Reputation: 2789

Sum() + DCount() combination in SQL not producing correct results

How can I get the total number of Granny Smith apples? I have tried everything and nothing seems to work. This is a cross tab query and I'm trying to count the number of Granny_Smith apples in each shipping container.

MS Access 2007 Expression

Total_Green_Apples: Sum(DCount("[Apple_Type]","[Apples]","[Apple_Type]"='Granny_Smith'))

SQL

TRANSFORM Count(DCount("[Apple_Type]","[Apples]")) AS Apple_Type
SELECT Shipping.Container_number, Sum(DCount("[Apple_Type]","[Apples]","[Apple_Type]"='Granny_Smith')) AS Total_Green_Apples
FROM Shipping INNER JOIN Apples ON Shipping.ID = Apples.ID
GROUP BY Shipping.Container_number
PIVOT Apples.Apple_Type;

Please help.

Upvotes: 0

Views: 525

Answers (1)

ben
ben

Reputation: 1936

If I'm reading your question correctly, you just need a count of all Granny Smith apples in all shipping containers?

I'm sort of guessing at your DB structure and here's what I come up with:

SELECT COUNT(Apples.ID) as Total_Green_Apples
FROM Shipping
    JOIN  Apples ON Apples.ID = Shipping.ID
WHERE Apples.Apple_Type = 'Granny Smith'

If you want the count of all Granny Smith apples in each container it'd be:

SELECT Shipping.ID, COUNT(Apples.ID) as Total_Green_Apples
FROM Shipping
    JOIN  Apples ON Apples.ID = Shipping.ID
WHERE Apples.Apple_Type = 'Granny Smith'
GROUP BY Shipping.ID

If you provide your table definitions I can tune the query.

Upvotes: 1

Related Questions