Getting Duplicates out of your table

[Shane0013]

I have a table with a date field and an account number field. I need to get rid of all the records that have the same date/acct combination...can anyone help me out on this?

 

[Darrylles]

Hi,

Create a 'find duplicate' query, then convert it to a 'delete' (after you have verified that the duplicates are being returned).

I ended up with following, which checked for f1 and f2 being duplicated.

DELETE test.f1, test.f2, test.pk
FROM test
WHERE (((test.f1) In (SELECT [f1] FROM [test] As Tmp GROUP BY [f1],[f2] HAVING Count(*)>1 And [f2] = [test].[f2])));

Remember that this deletes ALL duplicated records, it won't leave you with one of those duplicated records.

If you WANT to be left with one of each duplicated record, then DON'T select the key field in your find duplicates query, this will retrieve an 'example' single record of each duplicated record.
Convert this to an 'Append' query which adds each to a new temporary table.
THEN create the delete query as above.

Then append the temp table records back to the original.

Regards,

Darrylle


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk