# Thread: Need help with Min Max and Group query

1. ## 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.

2. 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

3. 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.

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

Corden (2015-02-25)

5. 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. 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. 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.

8. Here's one way - it's more complicated than Excel though!

9. 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. 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)

11. The weird quotation is actually a hyphen in their last name. What should I do?

12. Can you post a small database with data that doesn't work?

13. Hi Rory

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

zeddy

14. Let's start with the first one and see how we go?

#### Posting Permissions

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