Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Querying to find latest record only (Access 2003)

    How do you set a query to display only the lastest record for each criteria? e.g. product sales, show only latest sale for each product.

    Thanks.

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

    Re: Querying to find latest record only (Access 2003)

    Assuming that you have a date field in the table, you can do this with two queries:

    1. Create a query in design view, based on your table.
    Add the field identifying the product and the date field to the query grid. Don't add other fields here.
    Select View | Totals to turn the query into a totals query.
    Leave the Total option for the product field as Group By, and set the Total option for the date field to Max.
    This query will return the most recent sale date for each product.
    Save this query.

    2. Create a new query indesign view; add the table and the query you just saved.
    Join the table and the query on the product field, and also on the date field, so there will be two lines joining them.
    Add the fields you want to display from the table to the query grid.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Querying to find latest record only (Access 2003)

    You can use a GroupBy query (Select the Totals option from the menu), and set the column which contains the sales date for each item to Max. You may want to read the help for GroupBy queries to see all of the options.
    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
  •