Delete duplicates from a table

[Rogier]

how do i make a query to delete duplicates from a table. One of the records i
want to keep.

Please help me

best regard,

Roger

 

[Pat Hartman]

Since the records are duplicates, there's not really a way to do this with a
query. If you don't care which record gets saved, you can create a new
table with a compound primary key that will prevent duplicates from being
added. Then create an append query that copies data from the old table to
the new. At the end, you will get an error message telling you how many
records were not appended due to duplication. Click ok and the new table
will now be duplicate free. Delete the old table once you are sure that the
new table is correct.

 

[Allen Browne]

Use a subquery to identify which records to delete.

This example assumes:
- table named Table1
- primary key named ID
- duplicates defined by identical values in Field2 and Field3.

It keeps the one with the lowest primary key value.
If ID is an AutoNumber, this is most likely to be the oldest one.

DELETE FROM Table1
WHERE Table1.ID <>
(SELECT Min(Dupe.ID) AS MinOfID
FROM Table1 AS Dupe
WHERE Dupe.Field2 = Table1.Field2
AND Dupe.Field3 = Table1.Field3);

If more fields are involved in what defines "duplicate", just add more to
the WHERE clause in the subquery.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html