Deleting one record from the result of duplicate query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My Issue is that as a result of an append query I get duplicate records. The
Duplicate record has an auto number so I only want to delete the oldest
record which would be the min Value ,but I cannot get the query to work . Any
suggestions

Thanks
 
nadine said:
My Issue is that as a result of an append query I get duplicate records.

First make a backup of your table in case something goes wrong. You must
next determine which columns identify *duplicates* and place the primary key
or a unique index (disallowing nulls) on this combination of columns, but
*after* you remove these duplicates. In this example, ID is the autonumber
and all duplicate records with the same FirstName and LastName are removed,
leaving only the most recent records:

DELETE *
FROM TableA
WHERE (ID NOT IN
(SELECT MAX(ID) AS MaxID
FROM TableA
GROUP BY FirstName, LastName
HAVING COUNT(*) > 1)) AND
(ID NOT IN
(SELECT MIN(ID) AS MinID
FROM TableA
GROUP BY FirstName, LastName
HAVING COUNT(*) = 1));

After running this query the primary key should be placed on the LastName and
FirstName columns to prevent further duplicates.
 
Back
Top