Showing Latest Records in Query

[QandAdam]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Current[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]John Smith[/TD]
[TD]01/01/19[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]James Jackson[/TD]
[TD]22/12/18[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]LMN321[/TD]
[TD]Sam Franklin[/TD]
[TD]12/10/18[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]LMN321[/TD]
[TD]Danny Hudson[/TD]
[TD]25/12/18[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Current[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]John Smith[/TD]
[TD]01/01/19[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]LMN321[/TD]
[TD]Danny Hudson[/TD]
[TD]25/12/18[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]


...

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Current[/TD]
[TD]New Field[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]John Smith[/TD]
[TD]01/01/19[/TD]
[TD]Yes[/TD]
[TD]John Smith, James Jackson[/TD]
[/TR]
[TR]
[TD]LMN321[/TD]
[TD]Danny Hudson[/TD]
[TD]25/12/18[/TD]
[TD]No[/TD]
[TD]Danny Hudson, Sam Franklin[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

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.

 

[QandAdam]

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.

 

[Joe4]

Here is a link on Recordsets:
https://docs.microsoft.com/en-us/of...sktop-database-reference/recordset-object-dao

Here is an example like yours, where the combine one of the fields for multiple records:
http://allenbrowne.com/func-concat.html