Somebody please help! This is driving me mad!!!

I'm trying to produce a query that I can use to make a chart from, that will show how many records are counted for each month.

So far, so good.

But I run into a brick wall when I encounter a month for which there are no records. Rather than return a count of zero, it misses the month out all together, eg:

Jan 01 - 8
Feb 01 - 7
Apr 01 - 9

Where there are no records for March. Obviously, this is producing a graph that goes straight from Feb to Apr without droping down to zero for March.

So how do I get a query that will count records that aren't there, and return a count of zero for March? I've tried everything, joining it to a table of months, building a query on a query, writing a bit of code to loop every month, and I can't get anything to work.

Something at the back of my mind says that this should be easy - Am I missing something really simple and just making this more complicated than it is???

I had the same problem, only I was aggregating (count, sum) on years.

I created a table that only held a single field, one record for each year.

In a new query, I joined my [tblYearList] table to my [qryYearStats] with a left join that showed all records for [tblYearList], and only those in [qryYearStats] with matching records. Right-click on a relationship in query design view, select properties, and then you get a 3-choice dialog that gives you the available options.

I then added the appropriate fields to the query results, and then based my graph on that query.


I am sure you could do the same thing for months. You can easily manipulate the start and stop months for your graph by adding/deleting records, or adding a [GraphMe] Yes/no field in your [tblMonthList], and using [GraphMe]='yes' as a criterion in the above query.

[This message has been edited by KKilfoil (edited 03-15-2002).]