Results 1 to 5 of 5
  1. #1
    Ianto76 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2018
    Posts
    2

    Cool Latest record for all users


    I have a data base with multable names each name has multiple entries not all the same date I would like to be able to extract the latest record for all names

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Q1, get the latest index for each. In the query turn on summation (grouping)
    select userID, Max(Date) from table

    then make Q2, using Q1 to see all the fields:
    select * from table,Q1 where table.UserID = Q1.UserID and table.Date = Q1.Date

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Do an aggregate query by clicking the Totals button in the ribbon and include the ID field, name and other required fields.
    All fields will have Group By in the Totals row
    Change that to Max for the ID field if it's a number field.
    Otherwise use Max for the date field used in your query
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Ianto76 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2018
    Posts
    2
    Thanks very much it works perfect once I manipulated the information I wanted many thanks

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Not sure which solution you used, but on behalf of both of us, you're welcome
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Show latest dates of each record
    By Behedwin in forum Queries
    Replies: 5
    Last Post: 12-15-2017, 01:36 AM
  2. Select latest record for each Employee and Course
    By AccessPower in forum Queries
    Replies: 3
    Last Post: 12-16-2016, 01:09 PM
  3. Remove duplicate but keep the latest record
    By Compufreak in forum SQL Server
    Replies: 2
    Last Post: 06-12-2013, 11:55 AM
  4. Get latest record
    By Sweet16 in forum Queries
    Replies: 9
    Last Post: 09-23-2011, 01:39 PM
  5. Query latest record
    By asherbear in forum Queries
    Replies: 5
    Last Post: 08-02-2010, 03:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums