Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post
    Hi,
    I need to spot people with continuous service in an Access DB.

    Each person has an employee number and there is a record for every employment they've had with us. Mostly, this employment is continuous, but sometimes there is a break.

    For each employee number, I'd like a start of employment date & an end of continuous employment date.

    Example:

    Number - - - Start Date - - - End Date

    005 - - - 24/5/92 - - - 16/10/95
    005 - - - 17/10/95 - - - null
    006 - - - 15/10/98 - - - 25/12/05
    006 - - - 26/12/05 - - - 15/7/06
    006 - - - 16/7/06 - - - 15/7/07
    006 - - - 20/8/08 - - - 22/11/09
    007 - - - 10/12/6 - - - 17/12/07
    008 - - - 10/12/6 - - - null


    The output I'd like:
    Number - - - Start - - - End
    005 - - - 24/5/92 - - - null (because this person has had continuous employment and is still with us.
    006 - - - 15/10/98 - - - 15/7/07 The first period of employment with us
    006 - - - 20/8/08 - - - 22/11/09 The second (non continuous) period of employment with us
    007 - - - 10/12/6 - - - 17/12/07
    008 - - - 10/12/6 - - - null

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I don't see any way of doing what you want just using queries. I think this is going to require a VBA solution where you open the table and check each of the employment records for each person to determine if there is a break in employment, or if there is no gap between employment records. It basically involves opening a recordset using either DAO or ADO (I use DAO), and processing that recordset in employee order by date order, and constructing records in a second recordset that represent the output you are after. Is that to be presented in a form or report, or output as a table for subsequent use?
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thanks for your advice,

    This is part of an exercise to identify the top 100 employees with the longest period of continuous employment with no sick days.

    I think I'll just ignore employment breaks in the query and do it by hand on the output.

    Thanks again,

    Jim

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    An Interesting problem!

    I love interesting problems and thought I might be able to solve it in Excel ( you can export your Access data easily enough).
    Note: I had to change the dates to American style since I couldn't get my machine to use the European coding and recogiize them as dates shouldn't be a problem for you though)

    Here's the formula I used: =IF(AND(A2=A3,B3-C2<=1),"NB",IF(A2=A3,"Break",""))
    Just enter it into the Status (Column D) and drag it down.

    RG

    I had an additional thought. You could use conditional formatting using the Status column to color code the entries making it easier to spot the breaks.
    Attached Images Attached Images
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post
    That's excellent!

    I'd decided to try Excel this morning but I definitely wouldn't have come up with something as elegant as that!

    I'd decided to get all the dates for each employee number on one row with a macro & then look at gaps.

    I'm so glad I checked the website first thing!

    Cheers!

    Jim MacLeod
    Shetland Isles

Posting Permissions

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