Calculating Headcount by Month

[deodev]

Hi,

Below is the data I have in a table. I would like to create a headcount
query that would show headcout by month see below. Our Fiscal Year starts Nov
and ends in Oct. If a person starts anytime during the month, headcount is
one if terminated any time during the month then headcount is zero.

TABLE
Employee Hire Date Term Date
Emp1 6/1/2007
Emp2 8/5/2007 2/12/2008
Emp3 1/18/2008
Emp4 1/12/2008 5/16/2008
Emp5 9/5/2008 9/16/2008


Emp Hire Term Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sept Oct Total Avg
Emp1 6/07 - 1 1 1 1 1 1 1 1 1 1 1 1
12 1.00
Emp2 8/07 2/08 1 1 1 0 0 0 0 0 0 0 0 0
3 0.25
Emp3 1/08 - 0 0 1 1 1 1 1 1 1 1 1 1
10 0.83
Emp4 1/08 5/08 0 0 1 1 1 1 0 0 0 0 0 0
4 0.33
Emp5 9/08 9/08 0 0 0 0 0 0 0 0 0 0 0
0 0 0

 

[Michel Walsh]

If you have a table of month (those you want):

TheMonths ' table name
startDate ' field name, date_time field
06/01/2007
07/01/2007
08/01/2007
....
05/01/2008 ' data



Then, make the query:

SELECT DISTINCT startDate, Employee
FROM tableName INNER JOIN theMonths
ON theMonths.startDate <= tableName.TermDate
AND DateAdd("m", 1, theMonths.startDate) > tableName.HireDate


Save it, say, as q1, then use the crosstab:


TRANSFORM COUNT(*)
SELECT employee
FROM q1
GROUP BY employee
PIVOT startDate



Note that the DISTINCT is mostly used to count one employee just once, even
if he/she have been hired and terminated twice in the same month. Remove the
DISTINCT if you need to count such case as 2. Also, note that someone hired
and terminated the same month count as one, for that month. In fact, each
employee will count as one for each month of presence, partially or for the
whole month.



Vanderghast, Access MVP

 

[deodev]

Thanks

when I tried this I only get data for employees that have a terminated date
- I don't get the employees that are still here. Also, how do I exclude the
terminated employee from the count in the month the employee terminated for
example if terminated in Sept then the count would be Zero.

 

[Michel Walsh]

Something is wrong if you get only the last month.


With:


EmployeeHireTerminated Employee hired terminated
aa 2007.05.08 2007.09.12
aa 2008.03.03 2008.04.04
bb 2007.05.09 2007.05.12
bb 2008.03.03 2008.04.02



theMonths startDate
2007.05.01
2007.06.01
2007.07.01
2007.08.01
2007.09.01
2007.10.01
2007.11.01
2007.12.01
2008.01.01
2008.02.01
2008.03.01
2008.04.01



then

SELECT Employee, startDate
FROM EmployeeHireTerminated, theMonths
WHERE startDate <=Terminated AND DateAdd("m",1,startDate)>hired
GROUP BY employee, startDate


returns

Query1 Employee startDate
aa 2007.05.01
aa 2007.06.01
aa 2007.07.01
aa 2007.08.01
aa 2007.09.01
aa 2008.03.01
aa 2008.04.01
bb 2007.05.01
bb 2008.03.01
bb 2008.04.01



which clearly returns one record for each month of (partial) presence. Note the startDate refers to the table theMonths, NOT to the table of employee, and stand of the first day of each month to be considered.




If you should not consider someone terminated in a month, for the said month, then using a WHERE clause like:


WHERE hired <= startDate AND (DateAdd("m",1, startDate) -1 ) < terminated



should do.



Vanderghast, Access MVP

 

[deodev]

I only get data for terminated employees - NOT everyone - so if an employee
does not have a terminated date then it should count as one

 

[Michel Walsh]

Ha, indeed, in that case... Change TermindatedDate by Nz(TerminatedDate,
#01/01/3000#). That will temporary be equivalent to terminate the employee
in year 3000.


Vanderghast, Access MVP

 

[deodev]

that works - thanks
--
deodev

Ha, indeed, in that case... Change TermindatedDate by Nz(TerminatedDate,
#01/01/3000#). That will temporary be equivalent to terminate the employee
in year 3000.


Vanderghast, Access MVP

 

[deodev]

Michel,

The calculations worked for all the monhs from Nov 2007 through Oct 2008
except if the Employee start on Jan 2, 2008 through Jan 31, 2008. I get zero
and not one??
Below is the sql I have.

SELECT emphiredate.startdate, Data.EMPLOYEENAME, data.[TK]
FROM Data INNER JOIN emphiredate ON (data.hiredate<=emphiredate.startdate)
AND (DateAdd("m",1,emphiredate.startdate)<nz(data.termdate,#1/1/3000#));

 

[Michel Walsh]

Yes, indeed, the rule you stated was to not count the month where the
employee was terminated, isn't it?

Vanderghast, Access MVP

Michel,

The calculations worked for all the monhs from Nov 2007 through Oct 2008
except if the Employee start on Jan 2, 2008 through Jan 31, 2008. I get
zero
and not one??
Below is the sql I have.

SELECT emphiredate.startdate, Data.EMPLOYEENAME, data.[TK]
FROM Data INNER JOIN emphiredate ON (data.hiredate<=emphiredate.startdate)
AND (DateAdd("m",1,emphiredate.startdate)<nz(data.termdate,#1/1/3000#));
--
deodev

that works - thanks

 

[deodev]

yes it was - it is now working with the adjusted SQL below - thanks for
your help

SELECT emphiredate.startdate, Data.EMPLOYEENAME,
FROM Data INNER JOIN emphiredate ON
(DateAdd("m",1,emphiredate.startdate)<=nz(data.termdate,#1/1/3000#)) AND
(data.hiredate<DateAdd("m",1,emphiredate.startdate));

--
deodev

Yes, indeed, the rule you stated was to not count the month where the
employee was terminated, isn't it?

Vanderghast, Access MVP

Michel,

The calculations worked for all the monhs from Nov 2007 through Oct 2008
except if the Employee start on Jan 2, 2008 through Jan 31, 2008. I get
zero
and not one??
Below is the sql I have.

SELECT emphiredate.startdate, Data.EMPLOYEENAME, data.[TK]
FROM Data INNER JOIN emphiredate ON (data.hiredate<=emphiredate.startdate)
AND (DateAdd("m",1,emphiredate.startdate)<nz(data.termdate,#1/1/3000#));
--
deodev

that works - thanks
--
deodev


:

Ha, indeed, in that case... Change TermindatedDate by
Nz(TerminatedDate,
#01/01/3000#). That will temporary be equivalent to terminate the
employee
in year 3000.


Vanderghast, Access MVP


I only get data for terminated employees - NOT everyone - so if an
employee
does not have a terminated date then it should count as one
--
deodev


:

Something is wrong if you get only the last month.


With:


EmployeeHireTerminated Employee hired terminated
aa 2007.05.08 2007.09.12
aa 2008.03.03 2008.04.04
bb 2007.05.09 2007.05.12
bb 2008.03.03 2008.04.02



theMonths startDate
2007.05.01
2007.06.01
2007.07.01
2007.08.01
2007.09.01
2007.10.01
2007.11.01
2007.12.01
2008.01.01
2008.02.01
2008.03.01
2008.04.01



then

SELECT Employee, startDate
FROM EmployeeHireTerminated, theMonths
WHERE startDate <=Terminated AND DateAdd("m",1,startDate)>hired
GROUP BY employee, startDate


returns

Query1 Employee startDate
aa 2007.05.01
aa 2007.06.01
aa 2007.07.01
aa 2007.08.01
aa 2007.09.01
aa 2008.03.01
aa 2008.04.01
bb 2007.05.01
bb 2008.03.01
bb 2008.04.01



which clearly returns one record for each month of (partial)
presence.
Note the startDate refers to the table theMonths, NOT to the table
of
employee, and stand of the first day of each month to be considered.




If you should not consider someone terminated in a month, for the
said
month, then using a WHERE clause like:


WHERE hired <= startDate AND (DateAdd("m",1, startDate) -1 ) <
terminated



should do.



Vanderghast, Access MVP