Date problem

[lcan]

I am trying to get a count of Employees for each month of the year. 1. I am having to do seperate queries for each month (with grouping for Date and Identifier and a Count for Employee)for each table 2. then making a table with those numbers and 3. then querying just Employee for a Count. Is there a simpler way to do this with so many queries and tables????

Employee Date Identifier
John Smith Between 1/1/2002 and 1/31/2002 2222

 

[mdthornton]

You'll need to change column/table names etc but try something like this:

select [myEmp], format([myDate], "mm/yy&quot as mDate, [myId], count([myEmp])
from myTable
group by [myEmp], format([myDate], "mm/yy&quot, [myId]

or if you want to keep as a date/time column:

select [myEmp], dateserial(Year([myDate]), Month([myDate]), 1) as mDate, [myId], count([myEmp])
from myTable
group by [myEmp], dateserial(Year([myDate]), Month([myDate]), 1), [myId]

' converts all dates to 1st of month Best Regards,
Mike