Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find most recent value (2003)

    I have a Staff table with a StaffId (primary key) field. In a related table I list the StaffId, an EffectiveDate and Salary amount. I want to create a query to show the staff names,etc., from the Staff table alongside the most recent Salary amount. So, effectively, I'm showing their current salary. How can I construct the query to show this information? Thanks, Andy.

    (With apologies - I believe I may have asked this question a while ago, but have been searching and can't find it.)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find most recent value (2003)

    Create a query based on the related table.
    Add the StaffID and EffectiveDate fields.
    Select View | Totals.
    Change the Total option for EffectiveDate to Max.
    This query returns the most recent EffectiveDate for each StaffID.
    Save this query as (for example) qryMaxDate.

    Create a new query based on the Staff table, the qryMaxDate query and the related table.
    Join the Staff table to qryMaxDate on the StaffID field.
    Join qryMaxDate to the other table on StaffID and MaxOfEffectiveDate vs EffectiveDate.
    Add the staff data from the Staff table to the query grid, and the Salary field from the other table.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find most recent value (2003)

    Thank you. That makes a lot of sense. I can't check it at the moment but from past experience I'm sure it will work! Andy.

Posting Permissions

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