Sql help please

[bjreb]

I have a table that has almost duplicate data.
Ex.

Table name = Table

Key Name Time Location Mood
1 Bob 4/1/2007 12:30 Home Happy
2 Mike 4/1/2007 12:31 Store Sad
3 Bob 4/1/2007 12:32 Home Happy
4 Bob 4/1/2007 12:33 Home Sad

I would like to be able to eliminate all but one of the records where Name ,Location and Mood are the same.

This is what I have so far

select a.location,a.mood,a.name
from table a,table b
where a.time > getdate() - 20
and cast(a.time as smalldatetime) = cast(b.time as smalldatetime)
and a.name = b.name
group by a.Name,a.location,a.mood
having count(a.key) > 1

That gives me what I have duplicates. I can’t figure out how to delete the old ones and leave only the newest record.

Can someone help me please

Thanks
Andy

 

[MStef]

Hello bjreb!

I suggerst to make a Table2 (same as Table1), and a "Append query".
Look at "DemoEliminateDupliA2000.mdb".
Look at Table1, Table2 (primary key), Query1Append.
Run query an look at Table2.