Qry with dupes looking for only most recent date

[The Joker via AccessMonster.com]

Hello all,

I have a query I'm running and when it pulls it has a bunch of duplicates,
which is expected. Is there some type of criteria or something I can do to
only pull the most recent occurence of each result? Thank you!

 

[vanderghast]

Four ways to do it at http://www.mvps.org/access/queries/qry0020.htm


Vanderghast, Access MVP

 

[Daryl S]

The Joker -

If you post your SQL it would be easier for us to help.

There are two ways to do this, depending on what data you are pulling.
First case is a simple query with only key values and the date field:

Select EmployeeID, EmployeeName, ActionDate from EmployeeActionTable;

Change to this:

Select EmployeeID, EmployeeName, Max(ActionDate) from EmployeeActionTable;


The more complex situation is where you have multiple fields you want to see
that are different between the records, but you only want the latest-dated
record. These cases need a sub-query, or multiple Access queries. For
example:

Select EmployeeID, EmployeeName, ActionDate, ActionDescription,
ActionField2, ActionField3 from EmployeeActionTable;

Change to this:

Select EmployeeID, EmployeeName, ActionDate, ActionDescription,
ActionField2, ActionField3 from EmployeeActionTable
WHERE ActionDate = (SELECT Max(ActionDate) FROM EmployeeActionTable AS EA
WHERE EA.EmployeeID = EmployeeActionTable.EmployeeID);

It can get much more complex with joined tables...

If you need more help, you need to post your SQL and maybe a sample of the
duplicates you are getting.