# Thread: Total leavers and joiners for each month

1. ## Total leavers and joiners for each month

Hi
I have data that shows who works each month, I need to calculate employee turnover figures. I'd like a total of the people who leave each month, and who join each month.

In this example, employee A is long term, employees B and C leave in month 2, and employee D Joins us in month 3.
I'd like the query output to list the month and the number of leavers and joiners.

Employee___Month
A__________1
A__________2
A__________3
B__________1
B__________2
C__________1
C__________2
D__________3

Desired output
Month____Joiners____Leavers
1________n/a_______0
2________0_________2
3________1_________n/a

Sorry, but I'm just a bit stumped, I'm sure there's a simple way to do this by adding the first table to the query twice and querying for a non-match between [Month] and [Month]+1 but I can't seem to get it.

Thanks in anticipation!

Jim MacLeod
Shetland Isles

thanks

2. Wouldn't it be simpler if you just had a 2 fields for each Employee: DateJoined and DateLeft?

3. ## The Following User Says Thank You to MarkLiquorman For This Useful Post:

jmacleod (2015-03-06)

4. Hi Jim,
You might try using the Count function to obtain the number of rows returned by a query. Assume we've defined the variables current_month and previous_month. Then, a joiner would be an employee where (1) selecting by the employee and current_month yielded a count of 1, and (2) selecting by the employee and previous_month yielded a count of 0. Applying the Count function to the list of employees returned by this joiner query would yield the number of joiners.

The logic for determining number of leavers would be analogous.

The above is sketchy but might help you get started. A more detailed answer might or might not have applied to the database software and version you are using.

Dave

5. ## The Following User Says Thank You to DavidHLevin For This Useful Post:

jmacleod (2015-03-06)

6. Good to see you in the Lounge again - you've actually been here longer than I have! One question occurs to me in reading Mark's reply. Do you have people who work for a month or two, then don't for a month or two, and then work again for a few months. If so, you might want to have a separate table for working periods that links to the employee record for each person. Hope that helps.

7. ## The Following User Says Thank You to WendellB For This Useful Post:

jmacleod (2015-03-06)

8. Thanks everyone, that's given me a few ideas.

I'll post back what I end up doing.

Thanks again!!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•