Query - Unique and most recent

[Fredek]

Hi guys,

I would like to create a query that will deliver:

1. KRI_ID
2. DataDate
3. KRI Value

The difficulty is that I would like it to bring back each KRI_ID only once - only selecting the record with the most recent date available by using the DataDate field.

Any idea how I can get the query to do this?

 

[Joe4]

You can do this in a series of two queries:

1. Create an Aggregate (Totals) Query. This query should only have the KRI_ID and DataDate fields in them. Click on the Totals button, and change the "Group By" phrase under the "DataDate" field to "Max".

2. Create a second query, joining your first query back to your original table on BOTH the KRI_ID and DataDate fields, and returning whatever table fields you desire.

 

[Fredek]

Thanks Joe! I'll give it a go this week!