group by month in query and average date 1

[smiley0q0]

is there a way to group my dates by the month?

i have a table that has 2 years worth of dates.
it has DptName, EmpName, EmpTermDate, NumHrsWrkd

I have been asked to provide a report that shows by month a list of how many people quit in each department, the total hrs worked and an average date they quit during that month.

i can get everything except for seperating by month and the average dates. personally i think it's ridiculous, but the boss says thats what how he wants.
the only thing i can think of is to sort it by month some how, and then also try and just take the day and take an average of that and then throw the month in front of that number. but i have no idea of how to do that...

any suggestions?

Thanks
Smiley

 

[Golom]

Try

SELECT Format(T.EmpTermDate,"mm/yyyy") AS QuitMonth,
       T.DptName, 
       Count(*) AS Quitters, 
       Sum(T.NumHrsWorked) AS HrsWorked, 
       CDate(Avg(T.EmpTermDate)) AS AverageQuitDate

FROM Terminations T

GROUP BY Format(T.EmpTermDate,"mm/yyyy") , T.DptName

 

[smiley0q0]

Golom

you are the best!
thank you!!

Thanks for helping a rookie learn some more...


Smiley