Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Post 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
    Last edited by jmacleod; 2015-02-25 at 10:39. Reason: Tabs didn't show

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Wouldn't it be simpler if you just had a 2 fields for each Employee: DateJoined and DateLeft?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    jmacleod (2015-03-06)

  4. #3
    Star Lounger
    Join Date
    Jul 2013
    Location
    Murphy, NC
    Posts
    64
    Thanks
    0
    Thanked 8 Times in 8 Posts
    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. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

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

    jmacleod (2015-03-06)

  8. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    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
  •