Customize a Find Duplicates query

After creating the query with the Find Duplicates Query Wizard, you might want to modify the query design to produce different query results by adding fields, removing fields, or deleting duplicate records.

Add or remove fields

1. Open a query Design view, or open a form, report, or datasheet and show the Advanced Filter/Sort window.
2. Do one of the following:

   Add a field

   In a query, you add only those fields whose data you want to view, set criteria on, group by, update, or sort. In a filter, you add only the fields you want to use for sorting or specifying criteria, and Microsoft Access will automatically display all the fields in the filtered results.

   1. Select one or more fields.

      How?

      To select	Do this
      A field	Click the field name.
      A block of fields	Click the first field in the block, hold down SHIFT, and click the last field.
      Noncontiguous fields	Hold down CTRL as you click the fields.
      All fields	Double-click the title bar of the field list or click the asterisk (*).

   2. Drag the field from the field list to the column in the design grid where you want to insert it.

   Tip

   Instead of dragging, you can also add fields by double-clicking the name in the field list or selecting a field directly from the list box in the Field row on the grid.

   Remove a field

   • Select the field by clicking its column selector, and then press the DELETE key.

     Note   When you remove a field from the design grid, you're only removing it from the query or filter's design. You're not deleting the field and its data from the underlying table, nor, in the case of a filter, are you removing it from the filtered results.

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 the original and the duplicates. However, there is a way to automatically delete duplicate records while retaining the originals.

Automatically delete duplicate records

Deleting duplicate records from a table requires two steps. First, you create a copy of the structure of the table that contains duplicates, and then 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.

1. Copy the table and make primary keys of fields with duplicates.

   How?

   1. In the Database window, click Tables under Objects.
   2. Click the name of the table you want to delete duplicate records from.
   3. Click Copy on the toolbar.
   4. Click Paste on the toolbar.
   5. In the Paste Table As dialog box, type a name for the copied table, click Structure Only, and then click OK.
   6. Open the new table in Design view, and select the fields that contained duplicates in the table you copied.
   7. Click Primary Key on the toolbar to create a primary key based on the selected fields.
   8. Save and close the table.
2. Append only unique records to the new table.

   How?

   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're 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.
   8. Open the table to see the results.
   9. When you're sure the new table has the correct unique records, you can delete the original table, and then rename the new table using the name of the original table.