ACCESS Database: how to delete duplicate records?

The following is an example of a simple query which will delete all bar one of each subset of rows where the FirstName and LastName columns have, in combination, duplicate values:

DELETE *

FROM Contacts AS C1

WHERE ContactID <>

     (SELECT MAX(ContactID)

      FROM Contacts AS C2

      WHERE C2.LastName = C1.LastName

      AND C2.FirstName = C1.FirstName);

ContactID is the numeric primary key of the table.

To delete duplicate records in an Access Database, you can use a combination of queries to identify and remove them. Here's a general approach:

1. Identify Duplicates:
   • Create a query to identify the duplicate records based on your criteria.
   • Use the "Find Duplicates" or "Find Unmatched" query wizard.
2. Review Results:
   • Open the query to review the identified duplicate records.
3. Create a Delete Query:
   • Create a new query and change its type to "Delete Query."
4. Specify Criteria:
   • Specify the criteria in the delete query to target the duplicate records.
5. Run the Delete Query:
   • Execute the delete query to remove the identified duplicate records.
6. Confirm Deletion:
   • Be sure to make a backup before running the delete query, as it permanently removes records.

Always exercise caution when performing delete operations to avoid unintended data loss

select first(item_description),first(qty) from PiecesMinifigsSets group by item_description;

Before you can delete duplicate records, you have to define what constitutes a duplicate record. Is the record exactly the same in all fields? Or is the duplication on a part of record (i.e. Is it a person name that's duplicated or a company name or a part name?)

How you define what constitutes a duplicate, then you can decide how to identify and delete them.

For example, You have a table of people. You have a record for John Smith with an address of 12 Elm St and a John Smith at 35 Oak St. Are they the same person?

There are multiple ways to delete records, depending on context.

What is the context in which you need to delete records?

Please be as specific as possible.