Count Number Of Employees for Month and Year historically

[tighe]

how i would i get the number of employees for each month and year that we
have data for?

my fields are :
Representative_Name
Approval_Date
Termination_Date

my data goes back to 06/2007. i can get how many person's were hired/fired
in a particular month/year in history but a total number of un-terminated
persons up to that month in history is eluding me. hope that makes sense.

AC2007/XP.

 

[Dorian]

It depends on how you define 'the number of employees for each month'.
Do you measure at the start of the month or the end?
What do you want to do with an employee who starts and quits in the same
month?

SELECT COUNT(*)
FROM Employees
WHERE (TerminationDate > AsOfDate OR TerminationDate IS NULL)
AND Approval_Date <= AsOfDate;

AsOfDate is the date you are calculating from/to.
I assume Approval_Date is the hire date.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[tighe]

dorian,

thank you, but unless i am missing something for each month, i would have to
evaluate and requery to get my answer. i was hoping for answers like:
month/year:06/2007=5, 07/2007=9...12/2009=14.

 

[John Spencer]

There are a couple ways of doing this, but they involve having an auxiliary
table of some type. One way would be to build a table with a field YearMonth
and values in the table of 200701 to 200712 and 200801 to 200812 etc.

SELECT C.YearMonth, Count(C.YearMonth) as theCount
CalendarTable as C, YourTable as Y
WHERE C.YearMonth >= Year(Y.Approval_Date) * 100 + Month(Y.Approval_Date)
AND C.YearMonth <= Year(NZ(Y.TerminationDate,Date())*100 +
Month(NZ(Y.TerminationDate,Date())
GROUP BY C.YearMonth

You can do this other ways. For instance a table with two fields - the start
of the month and the end of the month.

OR a table of integers from 0 to 9 that you can use with the DateAdd or
DateSerial function to construct the months.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County