max for each person

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"geebee" (noSPAMs) wrote in message
...
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