Make another table of de-duplicated records.
1. In the Database window, on the Table tab, select the table you want to
deduplicate. Copy (Ctrl+C) and paste (Ctrl+V). Answer the question that you
want "Structure Only", and supply a name for the new table.
2. Create a query into your original table.
3. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.
4. Drag all the fields that define "duplicate" into the grid.
In the Total row, accept Group By.
A duplicate will be anything that matches *all* these fields.
5. Drag the primary key field into the grid.
In the total row under this field, choose Min.
The query will retain the lowest primary key value.
Choose Ascending in the Sorting row under this field.
6. If you have any other fields that are not part of what defines
"duplicate", drag them into the grid, and in the Total row choose First
under this fields. (At this point you might like to test that the results
look right.)
7. Change the query to an Append query (Append on Query menu.)
Answer the question that you want them appended to the table created in step
1.
Access adds an Append To row to the grid.
8. Make sure that the Append To row contains the correct field names for all
fields, particular those where you choose First in the Total row.
9. Execute the query (Exclamation icon on toolbar).
Read the confirmation dialog to make sure all records append okay.
At this point, you have a new table of deduplicated records. You can now use
the Unmatched query wizard to create a query that selects the duplicate
records. Then change that query to a Make Table (Make Table on Query menu)
to get the extra table of duplicates you wanted.
If the original table was involved relationships, you will need to break
those relationships (Tools | Relationships) so you can delete the table.
After deleting it, compact the database (Tools | Database Utilities |
Compact). If you then want to rename the deduplicated table to match the
original, make sure the Name AutoCorrect boxes are unchecked under Tools |
Options | General - otherwise Access is likely to get confused. You can then
recreate the relationships for the table.
