Using Count to return 0 when no records returned

[frogmann]

Hello, am trying a report that counts the number of Students who completed a specific lesson, grouped by month. The problem occurs when no students are counted for a month, instead of 0 it skips the month entirely. So the query:

SELECT Month([DateReceived]) AS Mnth, Count(tblStudent.StudID) AS CountOfStudID
FROM tblStudent INNER JOIN tblStudentLessons ON tblStudent.StudID = tblStudentLessons.StudID
WHERE (((tblStudentLessons.LessonID)=283) AND ((tblStudentLessons.DateReceived) Between #1/1/2010# And #12/31/2010#))
GROUP BY Month([DateReceived]);

returns:
Mnth CountOfStudID
January 1
March 1

when what I want it to return is:
Mnth CountOfStudID
January 1
February 0
March 1
April 0
May 0
etc.

I've tried various incarnations of Nz, using IIF conditions, nothing seems to quite get what I need.
Any help would be much appreciated. Thanks!

 

[Remou]

Ideally, you need a month table that can be left joined to the student table. You can then use Nz to return zero for nulls.

http://lessthandot.com

 

[frogmann]

Thanks Remou, I did it that way and it worked. Thanks for the help.

 

[jamaarneen]

Remou:
one thing that I don't understand. you mean a table with 12 records only (Jan ... Dec) ?
but what if Someone wants to get the above result for a period of 26 months?

5there is a thread I started going on, with a similar problem. you can respond there.)
Thanks
Ja

 

[PWise]

then you need a years table or a calander table