ACCESS Database: how to delete duplicate records?

Question

How do you delete duplicate records in the Access Database?

Thanks

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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?

Answer 5

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