max for each person

[geebee]

hi,

need to select the MAX centername for each person (determined by combining
date. and centername AS ID) , and join that to another table by the created
ID. the 2 tables will be joined on the created ID. there are more than one
centername for each person. but i want the most recent one to be selected
and then used as a subquery in a main query. just not sure the syntax to do
this, or best approaches.

thanks in advance,
geebee

 

[Allen Browne]

Use a subquery to restrict it to the particular record that matches the
person and most recent date.

Without knowing your schema, it will be this kind of thing:

SELECT ID, PersonID, CentreName
FROM Table1
WHERE ID = (SELECT TOP 1 ID
FROM Table1 AS Dupe
WHERE Table1.PersonID = Dupe.PersonID
ORDER BY Dupe.TheDate DESC, ID DESC);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

More examples:
http://www.mvps.org/access/queries/qry0020.htm