month by month query

[gazzy3k]

please help me

i have a date field called open & i need to generate a query to count all entries by month, so far i can get all cases each day, but i'm unable to get the query to count them just based on the month & year..

any ideas please

 

[Bat17]

create a new column in the query using CountField: (Year([open])*100)+Month([open])
and use that for your counting

edit
You may find it easier to create one field for month and another for year if you will need to filter the data by them

HTH

Peter

 

[EMP]

SELECT Format([Open],"yyyy mm") AS YearMonth, Count([TableName].[Open]) AS [Count]
FROM [TableName]
GROUP BY Format([Open],"yyyy mm");

^