Using WHERE with Count

[Dan Czarnecki]

Hi!

I am new here. Hope somebody can help me .

I have a database with two columns of Y and N (Yes / No). I need to be able to count the number of Y's for each column.

Putting in criteria as 'y' does not work, so I tried the statement below:

SELECT Master.category, Count([Master]![UPC]) AS Total,
Count(Tracker.TF_updated) AS CountOfTF_updated,
Count(Tracker.Allergen_updated) AS Allergens_Updated
FROM Master LEFT JOIN Tracker ON Master.UPC = Tracker.UPC
WHERE (((Tracker.TF_updated)='y'))
GROUP BY Master.category;


This works without the WHERE statement, but as soon as I add WHERE, it gets all screwed up.

SELECT Master.category, Count([Master]![UPC]) AS Total,

This selecets all my records so I can have a total count of each group.

Count(Tracker.TF_updated) AS CountOfTF_updated,

This should give me a count of only the 'Y' entries. It works fine as above, and gives a total of all non-null values. But I need it to count only the 'Y' entries. I cannot get this to work using the WHERE statement, it keeps giving me errors . Does anyone know how to count only the 'Y' values???

Count(Tracker.Allergen_updated) AS Allergens_Updated

This is the second column with Y/N values, this should also give me a count of only the 'Y' entries. It also works fine as above, and gives a total of all non-null values. But I need it to count only the 'Y' entries. I cannot get this to work using the WHERE statement, it keeps giving me errors . Does anyone know how to count only the 'Y' values???

FROM Master LEFT JOIN Tracker ON Master.UPC = Tracker.UPC

WHERE (((Tracker.TF_updated)='y'))

This statement when placed here does return only Y count. But... It returns a common count for Total and each column, and takes into consideration both columns, so only a count of entriess with y in both columns appears .

GROUP BY Master.category;

Anyone, please help! I have spent many hours on this and cannot get it to work .

Thank you,

Dan

 

[ecniv]

Y/N - Yes / No field usually is actually stored as True/False

your where clauses will filter those records returned by what you put in.

So it depends what you want to return.
YY
YN
NY
NN

I have a database with two columns of Y and N (Yes / No). I need to be able to count the number of Y's for each column.

You'd need to put two sub queries, one for each column. But, note that the combined total may not be the proper total (I mean you cannot add both together because of the YY records).

It would look something like:

Select 
  master.upc,
  qryCountTF.CountOfTF_updated
  qryCountAllergen.Allergens_Updated

From (master left join
(SELECT 
  Tracker.UPC
  Count(Tracker.TF_updated) AS CountOfTF_updated,
FROM 
  Tracker 
GROUP BY   Tracker.UPC
Having (((Tracker.TF_updated)=true))) as qryCountTF On master.upc=qryCountTF.UPC)
Left join
(SELECT
  Tracker.UPC
  Count(Tracker.Allergen_updated) AS Allergens_Updated
FROM 
  Tracker
GROUP BY Tracker.UPC
Having (((Tracker.Allergen_updated)=true))) as qryCountAllergen on master.upc=qryCountAllergen.upc

Have a play around with it?