Network Computer

          Systems, Inc.

Consulting services focused on security, design and installation.

Back To : On-Line Training Center

Finding and Deleting Duplicate Records in Access 2002

If your Microsoft Access database has data that has been imported from several sources, or if you have inherited a database that has been in use for many years and was not set up correctly, your database may contain duplicate records that you want to clean up.

To determine whether there are duplicate records in an Access table, you can use the Find Duplicates Query Wizard in Access. Also, if your table contains relatively few duplicates and you need to review the records before deciding which ones to delete, you can use the Find Duplicates Query Wizard to help you clean up the duplicate records. However, you can also delete duplicate records and keep the originals automatically.

To find duplicate records or field values

1. Open the database that contains duplicate records.
2. In the Database window, under Objects, click Queries and then click New on the Database window toolbar.
3. In the New Query dialog box, click Find Duplicates Query Wizard and then click OK.
4. In the list of tables in the wizard, select the table that contains duplicate records and then click Next.
5. From the list of available fields, select the field or fields that contain the duplicate information, such as a City field if you want to compare street addresses that are written differently for duplicate records. Click Next.
6. From the list of available fields, select the field or fields that contain the data you want to inspect among the duplicate records, such as an Address field. Click Next.
7. Enter a name for the query, such as Find duplicates for Customers, and then click Finish to run the query.

When you run the query, the records with duplicate fields (in this example, all records from the same city) are sorted by the duplicate field (City) so that you can compare data in any additional fields you want to inspect (such as Address). In this example, the Address field contains different data (for example, Ln. and Lane) for duplicate customer records.

If your database contains a small number of duplicates, you can select the individual records you don't want and delete them. After deleting duplicate records that do not use abbreviations in the Address field, the records now appear as follows.

However, you can't convert a Find Duplicates query to a delete query to delete duplicate records. This is because the Find Duplicates query returns the original record and any duplicates of the record so that you can choose which version to delete. Therefore, if you convert it to a delete query, it will delete both the original and the duplicates. However, there is a way to automatically delete duplicate records while retaining the originals if you don't need to choose which version to delete.

To delete duplicate records and keep the originals automatically

Deleting duplicate records from a table requires two steps. First, you create a copy of the structure of the table that contains duplicates and make primary keys of all the fields that contain duplicates. Second, you create and run an append query from the original table to the new table. Because fields that are primary keys can't contain duplicate records, this procedure produces a table without duplicate records.

To copy the table and make primary keys of fields with duplicates

1. Select the table in the Database window.
2. Click Copy on the toolbar.
3. Click Paste on the toolbar.
4. In the Paste Table As dialog box, type a name for the copied table, click Structure Only, and then click OK.
5. Open the new table in Design view, and select the fields that contained duplicates in the table you copied.
6. Click Primary Key on the toolbar to create a primary key based on the selected fields.
7. Save and close the table.

To append only unique records to the new table

1. Create a new query based on the original table that contains duplicates.
2. In Query Design view, click Query Type on the toolbar and then click Append Query.
3. In the Append dialog box, click the name of the new table in the Table Name list and then click OK.
4. Include all the fields from the original table by dragging the asterisk (*) to the query design grid.
5. Click Run on the toolbar.
6. Click Yes when you receive the message that you are about to append rows.
7. Click Yes when you receive the message that Microsoft Access can't append all the records in the append query. This transfers only unique records to your new table and discards the duplicates.

Open the table to see the results. When you're sure the new table has the correct unique records, you can delete the original table and then rename the new table with the name of the original table.

Back To : On-Line Training Center