count by criteria

[alktrigger]

I am having issues with creating a counting function that counts only fields that meet the > 0 criteria. The columns consist of only numerical data and some null values. What do I need to change for the count function to work with my criteria?

This is the SQL provided by the query. I can see the issue with the ">0" statement being outside the count function, but when I fix the order of operation in the second set of code, same result

SELECT Count(tblReportDump.Wk1) AS CountOfWk1
FROM tblReportDump
HAVING (((Count(tblReportDump.Wk1))>0));

SELECT Count(tblReportDump.Wk1) AS CountOfWk1
FROM tblReportDump
HAVING ((Count((tblReportDump.Wk1)>0)));

 

[namliam]

Try putting the >0 in the where:
SELECT Count(tblReportDump.Wk1) AS CountOfWk1
FROM tblReportDump
where tblReportDump.Wk1>0

 

[alktrigger]

This worked, but to follow up, by doing it with the WHERE function, I wont be able to do the count on multiple columns without creating multiple queries, correct? (i.e. counting wk1 and wk2 ... wk9) So I will need to create 9 different queries to count the individual weeks... Or is there another way of doing this?

 

[namliam]

Yes your right... but you can do it in one query if you use a IIF construct

SELECT Sum(IIF ( tblReportDump.Wk1 > 0; 1; 0 ) ) AS CountOfWk1
FROM tblReportDump

You might need to replace the ; by , depending on your regional settings.