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

[Guest]

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

 

[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);

 

[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

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]