how do i select the most current record using effective date in ac

M

MC

I need to select the most recent record out of duplicate records using the
effective date - how do I do that in Access using a Query? Thanks. Marianne
 
K

Ken Snell \(MVP\)

Without more details, here is a generic query:

SELECT Tablename.*
FROM Tablename
WHERE Tablename.DateFieldName =
(SELECT Max(T.DateFieldName)
FROM Tablename AS T);
 
J

John Vinson

I need to select the most recent record out of duplicate records using the
effective date - how do I do that in Access using a Query? Thanks. Marianne
Click to expand...

Use a Subquery: as a criterion on EffectiveDate use something like

=(SELECT Max([EffectiveDate]) FROM tablename AS X WHERE X.fieldname =
tablename.fieldname)

where fieldname identifies the group of duplicates amongst which you
wish to find the most recent.

John W. Vinson[MVP]
 
Top