Query to find last date

[DHIGHTO]

Hey guys,



I'm trying to query to find the latest date of a file that has multiple dates. I need the criteria to be the last date, and not just display the last date so I can find the transaction. I thought Dmax may work, but I don't know what my criteria would be. I put an example below. I'm looking for the last date, which would be 5/5/2014. I can't put in the date as the criteria since I am using this for a large amount of files, and the dates differ.

File #	setupdate	transaction
100001	1/1/2014	1
100001	3/5/2014	2
100001	5/5/2014	3

	Thanks!

[JoeM]

Do an Aggregate (Totals) Query.

Start a new query, and add the setupdate field.
Click on the Totals button (looks like a Sigma).
This will add a "Totals" row under that field.
In the "Totals" row, change the phrase "Group By" to "Max".
This will return your latest date.

If you would like to return the other fields associated with this Max Date, then create a new query linking your original table to the Aggregate Query you just made on the Date field, and then return the fields you want.