Results 1 to 4 of 4

Thread: Showing Latest Records in Query

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Showing Latest Records in Query

    I have a query which displays records linked to companies based on the ID:

    ID Name Date Current
    ABC123 John Smith 01/01/19 Yes
    ABC123 James Jackson 22/12/18 No
    LMN321 Sam Franklin 12/10/18 No
    LMN321 Danny Hudson 25/12/18 No

    Is it possible to make the records show 1 line per ID (The most recent record based on the Date)? Example below:

    ID Name Date Current
    ABC123 John Smith 01/01/19 Yes
    LMN321 Danny Hudson 25/12/18 No


    ...

    If this is possible, is it also possible (It doesn't necessarily have to be within this query) to create another field to show every Name for each record associated to an ID? Example below:

    ID Name Date Current New Field
    ABC123 John Smith 01/01/19 Yes John Smith, James Jackson
    LMN321 Danny Hudson 25/12/18 No Danny Hudson, Sam Franklin

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    48,190
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Showing Latest Records in Query

    Welcome to the Board!

    For the first part, create an Aggregate (Totals) Query to return the latest Date per ID.
    To do this, simply add the ID and Date fields to a new query in Design View.
    Then, click on the Totals button (looks like a Sigma). This will add a Totals row under each field, with the phrase "Group By" under each field.
    Under the "Date" field, change the phrase "Group By" to "Max" to return the latest date.
    Save this query.

    Now create a new query, made up on the query you just made and your original table.
    Join these two together on BOTH the ID and Date fields.
    Return all the records from the original table.

    This will give you what you show in your second image.

    Regarding your other question, yes it is possible, but may be a little more complex.
    If you had no more than two records per ID, it could be done pretty easily. However, I imagine that probably isn't the case.
    I would probably use VBA and recordsets to loop through my records, and write out the results that I want.
    There may be other ways to do this, that is just the one I am familiar with.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Jan 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Showing Latest Records in Query

    Thank you the first part worked perfectly!

    Do you know how I could use VBA and recordsets for the second part? I'm new to VBA and I wouldn't know where to start with recordsets

    Quote Originally Posted by Joe4 View Post
    Regarding your other question, yes it is possible, but may be a little more complex.
    If you had no more than two records per ID, it could be done pretty easily. However, I imagine that probably isn't the case.
    I would probably use VBA and recordsets to loop through my records, and write out the results that I want.
    There may be other ways to do this, that is just the one I am familiar with.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    48,190
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Showing Latest Records in Query

    Here is a link on Recordsets:
    https://docs.microsoft.com/en-us/off...set-object-dao

    Here is an example like yours, where the combine one of the fields for multiple records:
    http://allenbrowne.com/func-concat.html
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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