Re: Access: A Query that only shows the lastest/Last record for each vendor
Sure. You would just first add it to both the SELECT and GROUP BY clauses of the query in blue, and then add another AND to the end of the JOIN to join on this field too.
Note that instead of using an embedded query like this, you can do this in two queries, which allows you to do it all using the Query Builder.
Query 1:
This is your Aggregate Query, where you only select the fields you want to group by and the field you want to aggregate by.
You select those fields, and hit the Totals button (looks like a Sigma).
This adds a Totals row with the phrase "Group By" under all three fields.
Change "Group By" to "Max" under the CreatedDate field to return the latest date for each grouping.
So, when this is finished, you have a listing of every unique Grouping, and the latest date for each grouping.
Query 2:
If you want other fields displayed from your table other than the three fields in the previous query, simply join the original table to Query 1, joining on ALL three fields from Query 1.
Then you can elect to return any fields you want from the original table.
That is what the embedded query I originally created does. It can be done either way.
Once built, you do not have to open Query 1 at all, just Query 2.