How to Remove Duplicates in Access
- 1). Open your Access database. Go to "Queries" and select "New."
- 2). Select "Find Duplicates Query Wizard" and select "OK" to start the query wizard.
- 3). Select the "Tables" radio button and choose the table you want to view duplicates from. Select "Next." Select the fields you want to search for duplicates. Select "Next."
- 4). Choose any additional fields you wish to view along with the query. These fields are for your convenience only and don't affect your query. Select "Next." Choose a name for your query or use the default name and select "Finish."
- 5). Remove duplicates manually by selecting each row you wish to delete and press the "Del" or "Delete" key on your keyboard. If you will need more than two or three minutes to delete duplicates, remove duplicates by creating a new table. Continue to Step 6 for this method.
- 6). Remove duplicates automatically. Close your query by using the red "X" at the top of the query. Go to "Tables." Copy the table with duplicates by highlighting the table and pressing "Ctrl" and "C" on your keyboard. Paste the table by pressing "Ctrl" and "V" on your keyboard. Choose a name for the new table and select "Structure Only." Select "OK."
- 7). Highlight the new table and select "Design View." Select the field you wish to prevent duplicates in. Right-click the gray box to the left and choose "Primary Key." This will prevent any duplicates in that field since a primary key must be unique. You can change the primary key for the table after you've removed all duplicates. Close the table to save changes.
- 8). Go to "Queries" and click "Design View." Select the table you created in Step 6 and choose "Append" as your query type. Query types are found in the toolbar with an icon looking like two small white squares next to the red exclamation point icon.
Drag the asterisk (*) at the top of the field list to the first field at the bottom of the design view window. Press the red exclamation point icon on the toolbar to run the query. Only unique records will be pasted into the new table. - 9). Rename the original table to "TableName Original Date" or something similar and change the new table to the original table's name. This will give you a backup in the event a duplicate wasn't actually a duplicate.
Source...