Tricky Analysis??

[stumped]

Maybe its just me!!



I have a table of helpdesk calls that goes back years. The table contains thousands of records each record containing a call open date and a call closed date.
I would like to create a query that will be give me the number of calls open on a month by month basis. Its more tricky than I expected. Anyone got any suggestions please?

Thanks.

[RayMilhon]

It's a 2 step process. First create a query that includes a column to convert the call open date to a Year Month format such as 201009. Call it callopenmonth or something like that. Then create a second query with 2 columns for callopenmonth 1 as a group by and 1 as a count. The reason for doing the Year Month format is for sorting if you need to display the year month differently include a column for the format you want.

Query1:

Select format([datefield],"mmm yyyy") As properformat, format([datefield],"yyyymm") as sortformat
from sometable

Query2:
Select properformat, sortformat, Count([sortformat])
from query1
Group By properformat,sortformat
order by sortformat

[stumped]

Ok, thanks.