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

    Need help with query in Access 2007

    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!
    Attached Files Attached Files

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Maybe you can get some inspiration here, a rather similar situation where I spent several hours and that you never bothered to reply to.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Corden View Post
    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...

  4. #4
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Corden View Post
    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!

Posting Permissions

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