Finding the Maximum Date in a Table

V

Vince

I am comparing data in a table that can have the same identifying criteria (Record ID, etc.) but will have different dates listed in the table for the same record. For example:

001, 2003-01-01
001, 2002-08-01

I want to be able to return a result where the more recent date, as in this simple case, 2003-01-01, will be displayed while eliminating the 'bad' record.
 
L

Les

Create a query with all the columns that you want. Then,
change it into a group by query. Instead of group by,
select max for the date field.
-----Original Message-----
I am comparing data in a table that can have the same
Click to expand...
identifying criteria (Record ID, etc.) but will have
different dates listed in the table for the same record.
For example:
001, 2003-01-01
001, 2002-08-01

I want to be able to return a result where the more
Click to expand...
recent date, as in this simple case, 2003-01-01, will be
displayed while eliminating the 'bad' record.
 
V

Vince

Thanks Les! We're a little closer

Now the result displays ALL of the records as maximum dates. I need to exclude any duplicate records while returning the maximum date. The problem lies in determining how to display the duplicated records (via count expression) but only including the most recent (or Max) date. Any thoughts

Here is a sample of the SQL

SELECT ID, Member, IPA, Product, Max(Effective Date) AS [MaxOfEffective Date
FROM [TableName
GROUP BY ID, Member, IPA, Product;
 
J

John Spencer (MVP)

Try

SELECT ID, Member, IPA, Product, [Effective Date]
FROM [TableName]
WHERE [Effective Date] =
(SELECT Max(Tmp.[Effective Date])
FROM [TableName] ast Tmp
WHERE tmp.ID = [TableName].Id)
 
V

Vince

We're getting warmer. Now I'm getting a message asking me to enter a parameter value for the 'Tmp.ID'. Does anybody have any suggestions?
 
L

Les

Double check that you have typed everything like John's
example. It works for me.

-----Original Message-----
We're getting warmer. Now I'm getting a message asking
Click to expand...
me to enter a parameter value for the 'Tmp.ID'. Does
anybody have any suggestions?
 
Top