Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Smile Need help with Min Max and Group query

    I have numerous records that repeat itself. What I am trying to do is determine how long each employee has been in a specific job. I typically would group by job code and do a min max on the start and end dates. However, the problem is some employees have returned to an old job they left. Therefore, how do I get the counter to start again when they return to an old job again? See my example attached and the expected results.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Corden,

    I've moved you post to Spreadsheets since that's what you are working with.

    Do you have any aversion to macro code to do what you want? If not I'll give it a go. To to this with formulas won't be easy or pretty if possible at all. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Assuming that if the next start date is not one day after the previous end date, it counts as a new job, you could add a new column A and use this formula:
    =IF(AND(B2=B1,C2=C1,D2=D1,E2=E1,F2=N(G1)+1),A1,N(A 1)+1)
    then group on this new job number.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. The Following User Says Thank You to rory For This Useful Post:

    Corden (2015-02-25)

  5. #4
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thank you Rory. The formula worked perfectly in Excel! Is there a way to add this formula into a query in Access? I would like to automate the job tenure in my Access database.

    Thanks again.

  6. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi,

    Rory gave me an awesome formula that worked perfectly in excel. However, I am looking to link my file and create the query in Access so I can always pull the most recent Job Tenure. Is there a way to do that in Access? Rory gave me this formula for excel, IF(AND(B2=B1,C2=C1,D2=D1,E2=E1,F2=N(G1)+1),A1,N(A 1)+1) BUT how do I create this in Access?

    Thanks much!

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Access tables have no real concept of record order so that kind of approach won't work. I'll have a think about a query approach but you might be better off posting in the Access forum.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Here's one way - it's more complicated than Excel though!
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #8
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Rory,

    I am trying to use what you sent me with my new dataset but I am getting this message "Syntax error (missing operator) in query expression ‘[PersNo]=10073210 and [Last name] = “D’amico” and [first name] = “joseph” and …..

    I simply copied and pasted the records into your sheet1. Same column names but more data and it is not working for me. Though, it is working perfectly with the two cases you sent over,

    Can you tell me what am I doing wrong?

    Thanks so much.

  10. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What happens if you use:

    [Last name] = "D''amico"

    instead? (I am assuming the weird quotation marks in your post are not actually in your query)
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #10
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts
    The weird quotation is actually a hyphen in their last name. What should I do?

  12. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Can you post a small database with data that doesn't work?
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Rory

    I've got loads of databases with data that doesn't work.
    How many do you want?

    zeddy

  14. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Let's start with the first one and see how we go?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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