Qry with dupes looking for only most recent date

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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

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


Vanderghast, Access MVP


"The Joker via AccessMonster.com" u17775@uwe wrote in message
news:a3d2d69829281@uwe...
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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

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.

--
Daryl S


"The Joker via AccessMonster.com" wrote:

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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

.