Eliminate duplicate records in Ms-Access

Deleting duplicates with Append Query

In the above example, only two duplicate records were found. But what if the Customers database consisted of thousands of records, and after running the Find Duplicates Query wizard, the results showed hundreds of duplicate records? Manually deleting all those duplicates from the query results table would be highly impractical. Instead, you can use Append Query to have Access delete them automatically.

First, create a copy of the structure of the table that contains the duplicates. Click on the table name Customers in the Database Window, and then click the Copy button in the toolbar. Next, click the Paste button, which will display the Paste Table As dialog box shown in Figure H. Enter a name for the copy of the table structure, as shown. Under options, select Structure Only. Click OK to create the blank Customers Without Duplicates table.

Figure H

Open the Customers Without Duplicates table in Design View and change its primary key to Street Address, as shown in Figure I. Making the Street Address field the primary key field will prevent Access from copying records to the new table that have duplicate street addresses.

Figure I

We are now ready to create an Append Query against the original table. Create a query in design view for Customers. Drag the asterisk (*) to the query design grid to include all fields from the original table. Then select Append Query from the query-type drop-down list, as shown in Figure J.

Figure J

In the Append dialog box, select the blank database Customers Without Duplicates, as shown in Figure K.

Figure K

Click the Run button. In the dialog box that asks whether you wish to append the records to the new file, click Yes. A dialog box similar to the one shown in Figure L will appear, indicating that some records could not be copied because there were duplicate values in the primary key field, in this case, the Street Address fields. Click Yes.

Figure L

The query results table will have only one record for each street address. When you are satisfied that the Customers Without Duplicates table is correct, you may delete the original table.