Delete Duplicate Records

[dvannoy]

I have somehow lost my key field in a table. now there are thousands of duplicate records. does access have a function on deleting duplicate records? My problem is, I would still need to leave one of each record in the DB.

how can i do this??


Thanks

 

[sko]

Couldn't you select distinct records and move them to a new table?

SELECT DISTINCT tblOld.* INTO tblNew
FROM tblOld;

 

[Gazer44]

Slightly longer winded but it works-

Create new query - Find duplicates query wizard.

After done, open this query and delete all unwanted records, leaving only the ones needed.

Try this out on a copy of the table first - cos when you delete records on a query it will alter the table.

If happy with results then delete orig table and use this one!

 

[cmmrfrds]

If you have a unique key on the table such as an auto number then you can use this approach.

delete from table as outertab
where outertab.autokey >
(select min (innertab.autokey)
from table as innertab
where outertab.yourdupkey = innertab.yourdupkey)

In this example, if the autokeys match (only one record) then it will not delete otherwise it will delete (multiples of yourdupkey). Of course, you should test before applying.