Select the Latest Record

[Guest]

I want to create a schedule that automatically updates, dependant upon the
date the user is viewing the report.

How do I create a query that will select the most up-to-date record, only
one record per person? The table will probably have multiple records per
person and there will be a submittal date.

 

[Marshall Barton]

I want to create a schedule that automatically updates, dependant upon the
date the user is viewing the report.

How do I create a query that will select the most up-to-date record, only
one record per person? The table will probably have multiple records per
person and there will be a submittal date.

If you only want to select the lateset record for one
person:

SELECT TOP 1 table.*
FROM table
WHERE table.personid = <id value>
ORDER BY table.datefield DESC

If you want to select the latest record for each person:

SELECT table.*
FROM table
WHERE table.datefield = (
SELECT Max(X.datefield)
FROM table As X
WHERE X.personid = table.personid)

 

[Guest]

Use a sumation query. Set TOTAL row to Last.

 

[Marshall Barton]

That will not work Karl. Last() relies on the incoming
records being sorted by the date field. But, the records
coming from a table are not guaranteed to be in any
predefined order.

Max() will do what I think you had in mind, but you would
need to use a Totals (Group By) query then. However, I
believe the OP wanted the entire record with the latest date
even if the other fields had different values, which Group
By can not do (unless you use it in a subquery in the Where
Clause).