How to Find Duplicate Records in Access

Access provides a Query Wizard that helps you find duplicate records within a few clicks.

Here, we will walk through the process of using the Query Wizard to find duplicate records in a table.

  1. Screenshot of the Query Wizard button highlighted.

    Launch the Query Wizard

    Click Query Wizard from the Create tab in the Ribbon.

    This launches the Query Wizard.

  2. Screenshot of the Query Wizard.

    Select the Duplicates Option

    Click Find Duplicates Query Wizard, then click OK.

  3. Screenshot of the Query Wizard.

    Select the Table or Query

    Select the table or query that you want to find duplicates from, then click Next >.

  4. Screenshot of the Query Wizard.

    Select the Field/s with Potential Duplicates

    In the left pane, select the field (or fields) that contain the potential duplicates, and click the arrow button (>) to move it across to the right pane.

    Once the correct fields are in the right pane, click Next >.

    You can also just double-click a field to move it between panes.

    Clicking a button with double arrows (eg >> or <<) will move all fields to the other pane.

  5. Screenshot of the Query Wizard.

    Select the extra Field/s to Display

    Here you get to tell Access which other fields you'd like to be displayed in the query results.

    So, move the appropriate fields to the right pane, just as in the previous step.

    Once the correct fields are in the right pane, click Next >.

    It helps to add fields that will help you distinguish between each duplicate, and that provides enough information about what to do with the duplicates.

  6. Screenshot of the Query Wizard.

    Name the Query

    Provide a name for the query (or use the one that Access suggests), and select whether you'd like to view the results immediately or modify the design.

    Then click Finish.

  7. Screenshot of the results with duplicate records.

    The Results

    The results will appear with any duplicate records.

    You can use this query to take further action (eg, delete the unnecessary records, run a report, etc).