Showing Latest Records in Query

QandAdam

New Member
Joined
Jan 12, 2019
Messages
30
I have a query which displays records linked to companies based on the ID:

IDNameDateCurrent
ABC123John Smith01/01/19Yes
ABC123James Jackson22/12/18No
LMN321Sam Franklin12/10/18No
LMN321Danny Hudson25/12/18No

<tbody>
</tbody>

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

IDNameDateCurrent
ABC123John Smith01/01/19Yes
LMN321Danny Hudson25/12/18No

<tbody>
</tbody>


...

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:

IDNameDateCurrentNew Field
ABC123John Smith01/01/19YesJohn Smith, James Jackson
LMN321Danny Hudson25/12/18NoDanny Hudson, Sam Franklin

<tbody>
</tbody>
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.
 
Upvote 0
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

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.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top