Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    chicago
    Posts
    26

    Unanswered: query calling up previous records

    I have a table which tracks only changes to an employee's salary/promotion/location. If there is no change in the field, then the field is null in the record. Here is an example table:

    NAME~TITLE~SALARY~PROMOTION~DATE~LOCATION
    Sanchez ~ AccII ~ 45,000 ~ checked ~ 07/01/04 ~ null
    Sanchez ~ null ~ 40,000 ~ null ~ 07/01/03 ~ null
    Sanchez ~ AccI ~ 35,000 ~ null ~ 07/01/02 ~ NYC
    Andres ~ AccIII ~ 52,000 ~ checked ~ 03/01/04 ~ null
    Andres ~ AccII ~ 45,000 ~ checked ~ 03/01/03 ~ null
    Andres ~ null ~ 41,000 ~ null ~ 03/01/02 ~ null
    Andres ~ null ~ 38,000 ~ null ~ 03/01/01 ~ null
    Andres ~ AccI ~ 34,000 ~ null ~ 03/01/00 ~ CHICAGO
    Jones ~ null ~ 40,000 ~ null ~ 09/01/04 ~ null
    Jones ~ null ~ null ~ null ~ 12/01/03 ~ NYC
    Jones ~ AccI ~ 35,000 ~ null ~ 09/01/03 ~ CHICAGO

    I need a query/report which shows the salary change when an employee is promoted:

    NAME ~ OldTitle ~ OldSalary ~ Date ~ NewTitle ~ NewSalary
    Sanchez ~ AccI ~ 40,000 ~ 07/01/04 ~ AccII ~ 45,000
    Andres ~ AccI ~ 41,000 ~ 03/01/03 ~ AccII ~ 45,000
    Andres ~ AccII ~ 45,000 ~ 03/01/04 ~ AccI ~ 52,000

    I can't seem to pull it this way. I think it has something to do with the null entries in the table. Any suggestions?

    Thanks, KT
    Last edited by KDev; 10-12-04 at 16:00. Reason: format

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Quote Originally Posted by KDev
    I have a table which tracks only changes to an employee's salary/promotion/location. If there is no change in the field, then the field is null in the record. Here is an example table:

    NAME~TITLE~SALARY~PROMOTION~DATE~LOCATION
    Sanchez ~ AccII ~ 45,000 ~ checked ~ 07/01/04 ~ null
    Sanchez ~ null ~ 40,000 ~ null ~ 07/01/03 ~ null
    Sanchez ~ AccI ~ 35,000 ~ null ~ 07/01/02 ~ NYC
    Andres ~ AccIII ~ 52,000 ~ checked ~ 03/01/04 ~ null
    Andres ~ AccII ~ 45,000 ~ checked ~ 03/01/03 ~ null
    Andres ~ null ~ 41,000 ~ null ~ 03/01/02 ~ null
    Andres ~ null ~ 38,000 ~ null ~ 03/01/01 ~ null
    Andres ~ AccI ~ 34,000 ~ null ~ 03/01/00 ~ CHICAGO
    Jones ~ null ~ 40,000 ~ null ~ 09/01/04 ~ null
    Jones ~ null ~ null ~ null ~ 12/01/03 ~ NYC
    Jones ~ AccI ~ 35,000 ~ null ~ 09/01/03 ~ CHICAGO

    I need a query/report which shows the salary change when an employee is promoted:

    NAME ~ OldTitle ~ OldSalary ~ Date ~ NewTitle ~ NewSalary
    Sanchez ~ AccI ~ 40,000 ~ 07/01/04 ~ AccII ~ 45,000
    Andres ~ AccI ~ 41,000 ~ 03/01/03 ~ AccII ~ 45,000
    Andres ~ AccII ~ 45,000 ~ 03/01/04 ~ AccI ~ 52,000

    I can't seem to pull it this way. I think it has something to do with the null entries in the table. Any suggestions?

    Thanks, KT
    You need to pull data when the promotion is checked. that way (as in your example) you will get one record for Sanchez and two records for Andres.

  3. #3
    Join Date
    Mar 2004
    Location
    chicago
    Posts
    26

    I get incomplete info that way

    I tried to filter on the Promotion field, and i do get the 3 records of when they got promoted. However i do not get their old salary, which are referenced in records where the promotions box was unchecked. Any suggestions are greatly appreciated. thanks....kt

  4. #4
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Well you need some improvisation here.You can accomplish this by having two queries. One query will pull all the data where promotion is checked call it Qry1. Then make another query based on the table and the qyery Qry1 and pull all the reqd. data when joined on the Name field.

Posting Permissions

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