Eliminating duplicate entries by getting only the most current entry

[amy3000]

I have a table with multiple entries for each person. There are multiples because each person may have been admitted multiple times to our agency. I have no control on how this data was set up so I'm stuck with this table.

How do I do a query in which I say I want a unique record for each person and if there are multiple entries for someone just the most current admission?
I know there must be a way to set this criteria in the admission date field of the query but I don't know how to do it. Thanks.

 

[PHV]

Something like this ?
SELECT A.* FROM yourTable INNER JOIN
(SELECT [person field], Max([admission date]) AS LastDate FROM yourTable GROUP BY [person field]) AS B
ON (A.[person field]=B.[person field]) AND (A.[admission date]=B.LastDate)
;

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

 

[amy3000]

Thanks PH. I did a little more searching around too before reading this. This is probably the same thing without the sql language. I did a totals query, grouped by ID number and set admission date to MAX. I don't find any duplication so it looks like it worked. That was a pretty easy one!