how to find the latest date in a table

[cthompson]

How do I find the records with the latest date even if it is a few years ago. In this situation i have multiple entries at various dates, I wish to find the last entries made and ignore any earlier entries.

 

[jeffreybrown]

How is you data setup and how do you want to find this record?

Dates in one column?

Do you want the record highlighted or just displayed?

 

[cthompson]

Hi Jeff,
Thanks yes all the dates are in one column (field) and I want to make a new table with only the latest dates the criteria will be based on an employee ID number

 

[Joe4]

You don't really need to make a new table, you can just use a query to get what you want.
What you want is an Aggregate (Totals) Query, where you "Group" records on certain fields, and then apply some function to one of the other fields. You would do this for things like counting records by person, summing records by person, or taking the earliest record for each person.

- To do this, simply create a new query.
- Add your Employee ID and Date field.
- Click on the Totals button (looks like a Sigma).
- On the new "Totals Row" it lists under each field, change the one under your Date field from "Group By" to "Min".
- View your results.

This should give you what you are looking for.

If you want to see all of the other fields associated with this record:
- Create a new query, adding your Aggregate Query and original Table
- Join the two objects (your Table and Query) on both fields found in the Aggregate Query
- Return any (or all) of the fields you want from your Table.