Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    displaying latest 50 records (Access 2003)

    My table has machine #, date/time stamp, and other fields containing measurement data. I want to find the latest 50 records for each machine #. When I set Top Values to 50 in a query, it returns just the last 50 records. But I need it to find the last 50 for machine 1, then the last 50 for machine 2, etc. I tried Grouping and Sorting in reports, but that didn't get it either. It seems like this should be simple to do, but I keep running into a brick wall.

    Any ideas will be appreciated!

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

    Re: displaying latest 50 records (Access 2003)

    In a query:

    Create a query based on the table.
    Add the fields you need, including the timestamp field.
    Set the criteria for the timestamp field to

    In (SELECT TOP 50 t.[Timestamp] FROM [NameOfTable] AS t WHERE t.[Machine#] = [NameOfTable].[Machine#] ORDER BY t.[Timestamp] DESC)

    You must, of course, replace the names Timestamp, NameOfTable and Machine# with the actual names of the fields and table. Order the query by Machine# (Ascending) and by Timestamp (Descending).

    In a report:

    See How to create a top values per group report in Access 2002 (applies to Access 2003 too)

  3. #3
    New Lounger
    Join Date
    Aug 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: displaying latest 50 records (Access 2003)

    Wow! I went the query route, and this works like a charm.
    Thanks so much. I would never have gotten this on my own.
    THANK YOU!!

Posting Permissions

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