Latest Date

[M8KWR]

I have a table with multiple entries per ID number.

What i want is the query to show only the latest Date entry for each ID number.

So if i have for example
IDNo Date
1 01/07/05
1 27/06/05
2 05/07/05
2 04/05/05
3 01/09/05
3 25/08/05

I need it to just show

IDNo Date
1 01/07/05
2 05/07/05
3 01/09/05


I hope i have explained myself ok, many thanks for the help in advance.

 

[PHV]

SELECT IDNo, Max(Date) AS LatestDate
FROM yourTable
GROUP BY IDNo;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[M8KWR]

i thought it was that, many thanks for the quick response.

 

[erfigge]

Can you explain this? Did you do this within the query critera?

 

[lespaul]

You can paste this query in the SQL view, modify the table and field names, switch back to the QBE and "see" how the criteria is set up.