Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a table which holds salary information about employees, each of whom can have multiple entries. The 'latest' salary record for an employee is identified by the highest value Salary_ID (an Autonum field) for this employee. The salary in this latest record can be less than previous recorded salaries. The requirement is to write a query which extracts this latest record for a particular employee.

    I produced a totals query to test this, asking for MAX Salary_ID and parameterised the Employee_ID to pick out the employee I wanted; the salary field was a Group By field.
    This results in all three salary records for the chosen employee being returned.
    Clearly I am not understanding how relational DBs work or how MAX works.

    Why did asking for MAX Salary_ID for a particular employee deliver all three salary records? Why didn't it produce the one record which had the highest Salary_ID for them?
    And what is required in the query to get this single record, please?
    Thanks
    Silverback
    Silverback

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='silverback' post='799979' date='26-Oct-2009 17:51']I have a table which holds salary information about employees, each of whom can have multiple entries. The 'latest' salary record for an employee is identified by the highest value Salary_ID (an Autonum field) for this employee. The salary in this latest record can be less than previous recorded salaries. The requirement is to write a query which extracts this latest record for a particular employee.

    I produced a totals query to test this, asking for MAX Salary_ID and parameterised the Employee_ID to pick out the employee I wanted; the salary field was a Group By field.
    This results in all three salary records for the chosen employee being returned.
    Clearly I am not understanding how relational DBs work or how MAX works.

    Why did asking for MAX Salary_ID for a particular employee deliver all three salary records? Why didn't it produce the one record which had the highest Salary_ID for them?
    And what is required in the query to get this single record, please?
    Thanks
    Silverback[/quote]

    You might find that LAST will do it.
    But beware it is the last in order based upon the underlying data set so you need it sorted by the autonumber field
    Andrew

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    When using a Group By query, you have to be careful not to include too many variables. Since you included the salary field, and they were presumably different, you get all of the records. What you typically want to do in your case is return only the record ID and the employee ID in your query. Then if you want to know the salary, you create a new query which joins to the Group By query using the employee ID and the salary record ID to the Salary record. In you case you could probably just join by the salary ID record.
    Wendell

Posting Permissions

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