Need help with query in Access 2007

[Corden]

Hi,

I have been driving myself nuts trying to get the results I am looking for with no prevail. So I am hoping someone can solve my issue...
I have a table in SAP called organizational table that contains job data for each employee. The issue is the way they store the data. For example, when someone gets a new title, the data person goes in and creates a new title with a new date but keeps the same Job code. What I am trying to figure out is how long has an employee been in a job regardless of job code. See my excel file attached with the example. I did a query with empid, name, job code, and jobtitle and grouped it by all fields with a min start date and max end date. But then I don't get all the records. Frustrating.....

Does anyone have any ideas on how I can determine how long an employee has been in a job regardless of the job code but should only consider it if the jobtitle has changed? It is also most like I have to search through each record and look for a change then log it on a separate sheet.

Thank you!

[ruirib]

Maybe you can get some inspiration here, a rather similar situation where I spent several hours and that you never bothered to reply to.

[jeremybarker]

Hi,

I have been driving myself nuts trying to get the results I am looking for with no prevail. So I am hoping someone can solve my issue...
I have a table in SAP called organizational table that contains job data for each employee. The issue is the way they store the data. For example, when someone gets a new title, the data person goes in and creates a new title with a new date but keeps the same Job code. What I am trying to figure out is how long has an employee been in a job regardless of job code. See my excel file attached with the example. I did a query with empid, name, job code, and jobtitle and grouped it by all fields with a min start date and max end date. But then I don't get all the records. Frustrating.....

Does anyone have any ideas on how I can determine how long an employee has been in a job regardless of the job code but should only consider it if the jobtitle has changed? It is also most like I have to search through each record and look for a change then log it on a separate sheet.

Thank you!

Corden,

I imported your data into Access and a standard grouping query seemed to give what you wanted - here is the SQL:

SELECT JOBDATA.Empid, JOBDATA.[Last name], JOBDATA.[First name], JOBDATA.JobTitle, First(JOBDATA.Job) AS FirstOfJob, Min(JOBDATA.[Start Date]) AS [MinOfStart Date], Max(JOBDATA.[End Date]) AS [MaxOfEnd Date]
FROM JOBDATA
GROUP BY JOBDATA.Empid, JOBDATA.[Last name], JOBDATA.[First name], JOBDATA.JobTitle;

The fields will come out in a different order from your summary because of the grouping requirement, but you can build another query on top of this one to get the final output you want.

From the small sample of data you've provided, I can't see why any records would be omitted from a standard totals query of this sort...

[Corden]

Thank you for your support. It does work as you suggested but I need to also add department. But when I include department into the group by, it changes the dates for my job data. How can I include it without it causing a date change.

[jeremybarker]

Thank you for your support. It does work as you suggested but I need to also add department. But when I include department into the group by, it changes the dates for my job data. How can I include it without it causing a date change.

The only options you've got in that case are to use First (as I did with Job) or Min/Max (as we're doing with the dates). The question you should really be asking is "how often is department changing?" because when you introduce it into a Group By it may change the groupings as you've seen!