Can Count return a zero?

[Wayne Cramer]

I have three queries that count non-compliant records in each of three catagories per month. One catagory has records for both Jan and Feb and returns the rersults as follows:
Jan 3
Feb 4
The other two vatagories however have no non-compliant records for January so they return as follows:
Feb 2
Feb 1
I wanted to tie the three queries together in the form for reporting:

Cat 1 Cat 2 Cat 3
Jan 3 0 0
Feb 4 2 1

Is there any way for the queries to return a zero for any month with no records rather than not returning the month at all? Thanks for any help.

 

[georgedwilkinson]

Yup, union your query with:

Myquerywithoutcount(*), '0'
where
mycriteria not in (select matchingcriteria from theothertable);

 

[georgedwilkinson]

I'm sorry, I didn't read your post thouroughly enough.

My earlier advice is still slightly valid but you also need to do a crosstab query.