Can't find a way to count employees by month including additions and subtractions

[randomdude]

I'm currently designing a database to track headcount and headcount changes for a building of roughly 1500 employees (advisors) and I've run into some issues.

Getting a total headcount of employees under each manager is easy, I just count the records of employee id's and group by manager name, but I'm having a problem making this data work with employee changes. I have a table called [advisor changes] that tracks an employees id if they go on a project or a loan to another dept for instance.

So if employee A goes on a project from team A to team B for a certain time frame, like 9/1/09-10/5/09 then that means Team A is temporarily losing an employee and team B is gaining one. When a project is entered a start and end date are also entered for that record.

The problem is I can't find a way to show total headcount by month where I can take the total employee headcount and then add anyone joining for a project and subtract anyone leaving for a project. I can get a total BUT not by month, and this is where my question comes in.

When I get total employee headcount by counting every employee's id, this is in no way tied to a month. Because of this I can't get the query to work where I get a monthly total by taking employee headcount and subtracting employees on project for the dates they were gone. Employees being added or subtracted have dates associated with them, but counting an employee id has no date associated with it.

I don't know if this is a query problem or a table problem, but I'm thinking there should be a way to design the tables to make it work.

Ultimately what I'd like to generate is a report saying something like for September team A had 50 employees, they gained 1 on project and lost two so they had a total of 49 employees for September. and I need to do that for every month so we can track it for the year.

Any help would be greatly appreciated.

 

[namliam]

Query problem, the problem is you dont have a month or date for the default department... simply because well it starts 01-jan-2000 and ends never (or when he finaly does leave).

Then you have a change from 05-dec-2008 to 20-Mar-2009, where you have the starting and ending months... but again not the stuff inbetween.
Instead of...
EmpID dept Start End
1 a 01-jan-2000 31-12-2999
1 b 05-dec-2008 20-mar-2009

You would want/need
EmpID dept Start End
1 a jan-2000
1 a feb-2000
1 a mar-2000
...
1 a jan-2008
1 a feb-2008
1 b jan-2009
1 b feb-2009

Then its easy to count... so try and work towards that goal using some queries see how far you can get

Post back if you run into troubles.

PS Welcome to AWF

 

[randomdude]

I'm not quite clear on that logic. If every employee shows a start date and end date, then for all current employees who haven't been terminated they would only have one date associated with them. So how would I turn that date into a monthly count?

Logically it makes sense to say if employee A started 2/1/05 and he hasn't been fired, then he's been an employee every month since that date. But i can't get access to see it that way.

For instance if I just take employee additions and subractions through projects and forget about total headcount, I still can't get the numbers I need.

If in October team A had 2 employees join through project and in November they had 1 employees leave through project I want to pull a query saying for october, teams A, B, and C had this many employees join and this many leave, resulting in a net gain/loss of X. But I've found that if a team doesn't have BOTH an employee leaving and joining in the same month, then the query shows no record. Basically meaning if they gained 2 employees in October but didn't lose any, then it can't subtract 2-0 because no record exists for an employee leaving in October.

I've tried working with the nz function, but that only seems to work if a field is null, but not if a record doesn't exist at all for the month. Does that make sense? I can't figure out how to have the query group by October and subract a record that doesn't exist for October.

 

[randomdude]

Thanks for the info namliam, what you said got me started down the right path to getting a total employee headcount by month.

The issue i'm having now is what I mentioned in my last reply, where I can't find a way to get a total of employees joined through project minus employees lost through project, if the dept didn't have an addition and a loss in the same month.

I'm probably not doing this right, but my [employee changes] table has these fields. [employee change id], [headcount change id], [employee id], [change start date], [change end date], [dept name], and then I have [change dept name] (which is the dept they're going to on their project).

I also have a Months table with data similar to what you had mentioned.

So in my query to get total employees out for the month I'll have [enter period] in the criteria of the period field from the months table, and then I do a count of the [department name] field which will show how many advisors left and I group by the [department name] field from the departments table. Does this sound messed up? As i'm typing this it doesn't sound right. But it does give me the right number of employees by dept who left that month.

I do the same thing to get a total of employees who joined a dept during the month, but instead I count the [change dept] field.

But again, the problem is if Dept A had 3 people leave in october, but they had 0 join in october then the query that totals employees joining has no record at all for Dept A for october so when I make a 3rd query to subtract employees who left from employees who joined, Dept A shows no record at all (instead of the correct answer of 3, since 3-0 is of course 3).

Any suggestions?

 

[namliam]

If in October team A had 2 employees join through project and in November they had 1 employees leave through project I want to pull a query saying for october, teams A, B, and C had this many employees join and this many leave, resulting in a net gain/loss of X. But I've found that if a team doesn't have BOTH an employee leaving and joining in the same month, then the query shows no record. Basically meaning if they gained 2 employees in October but didn't lose any, then it can't subtract 2-0 because no record exists for an employee leaving in October.

This is exactly your / the problem, you want to show data where there is none, this requires some trickery...

Thanks for the info namliam, what you said got me started down the right path to getting a total employee headcount by month.

Great keep chugging away at it

I also have a Months table with data similar to what you had mentioned.

This part is KEY you need this "dummy" table with dates to reproduce the month data you need.

Any suggestions?

Actually there is a design flaw hidden here, you have the same information in 2 places...
The "default" department and the "changed" department are some place else...

The trick is to work with the dummy months to build up your data...
If you have start-stop dates for each and every department, be the "fixed"/default depts or "loaned to" depts... Then this puzzle is half solved.
You either have to "fake" this data or actually make it... making it make sence to me (i.e. fixing the design issue )

 

[DCrake]

Having quickly read this posting I would like to offer my thoughts. Basically if you only need to tknow the head count for each dept you could create a table that holds the code for each department for each day of the month

such as

Dept Id
Date
HeadCount

Now if a person moves from dept a to dept b on a specific date then you would update the headcount for that date for that dept. So dept a would reduce by one and dept b would increase by one.

But then I ask myself what happens if they move half way through the day? both depts will have .5 wte. You would also need to monitor hatchings matchings and scratchings as well.

so by grouping by dept and by month you would get the headcount totals.

David

 

[namliam]

Basically if you only need to tknow the head count for each dept you could create a table that holds the code for each department for each day of the month

Please tell me you are NOT suggesting to store calculated values???

 

[namliam]

I have created a pretty quick Quick and Dirty example of how to make Dummy months of which you can report...

See attached zip file

A2000 format

 

[gemma-the-husky]

if you cant get this simply with a query


i would be inclined to store this in a temporary table

column heads
start_of_month joiners leavers end_of_month

rows
month or date


process the date set to populate the table - this will need a bit of code, or maybe several append/update queries - needs some logical thought anyway.

this is probably similar to DCrake's thinking

---------
i do something similar to control invoices/despatches - so i can reconcile invoies raised in a period with despatches

i have a table with

1 - invoices raised in a given period
2 - invoices raised after cut off for despatches in period
3 - invoices raised in period for despatches pre-dating period
4 - despatches for a given period

to prove that 1+2-3 = 4 - and thereby provide a useful checking mechanism for accounts accruals etc

this is a similar thing