Finding the Maximum Date in a Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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;
 
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)
 
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?
 
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?
 
Back
Top