How to display a count of zero from a query?

[Guest]

I have a query where I am counting values that are between 1 and 10000 by
group and month. For some of the groups in a given month there are no values
between 1 and 10000 to count and the record does not display in the query.
Therefore, how can I set up the query to display the group and month with a
zero count.

Thank you,
MisterH

 

[Guest]

Don't restrict the query on the range of values and instead of using the
COUNT operator use the SUM Operator with an expression which returns 0 or 1
depending on whether the value is within the range, e.g.

SELECT YEAR(Mydate) As MyYear, MONTH(MyDate) As MyMonth,
SUM(IIF(MyField BETWEEN 1 AND 10000,1,0)) As MyCount
FROM MyTable
GROUP BY YEAR(Mydate), MONTH(MyDate);

Ken Sheridan
Stafford, England