3
readers  helped!

This helped me

How to Find Duplicates Easily in Microsoft Access

Duplicate records can appear if you have multiple people entering data into a database without enough safeguards. Merging several databases together can also cause duplicates. Access provides a query tool to find duplicates in your database. You can then remove or merge them, making your database easier to read and more effective.

1

Know what makes data "duplicate".

  1. For example, a customer that was entered into the database twice may have two different IDs and potentially different spellings. On the other hand, if the name is common it could be two different customers. You'll need to compare the data available and check each result carefully to determine what is and isn't duplicate.
    Advertisement
2

Backup your database.

  1. This way you can restore the database if you accidentally delete the wrong entries.
    • Click the File menu and select "Save As" or "Save & Publish".
    • Click "Backup Database" in the Advanced section. Follow the prompts to backup your database.
3

Inform other users that you're about to make changes.

  1. This isn't required, but can save you some headache later if things go wrong.
    • Set your database to Exclusive mode if you can. This will prevent any changes from being made by other users. Click the File menu and select "Options", then select "Client Settings". In the "Default open mode" section, select "Exclusive". If you don't have many people using the database, you generally don't need to worry about this.
    Advertisement
4

Open the Query Wizard.

  1. The process for starting the wizard varies depending on the version of Access you are using: [1]
    • 2013/2010 - Click the "Create" tab and then click "Query Wizard".
    • 2007 - Click the "Insert" or "Create" tab and select "Query Wizard".
    • 2003 - Open the Database window and select the "Queries" tab. Click the "New" button.
5

Select the "Find Duplicates Query Wizard" option.

  1. Advertisement
6

Choose the table you want to search.

  1. Select the table you want to check for duplicates.
    • For most duplicate checking, you'll want to keep the "Tables" view selected.
7

Select the fields you think contain duplicates.

  1. Include enough fields to make a judgment. Duplicates are only returned if the fields match character for character. You can use expressions to find partial matches. [2]
    • Avoid using general fields. Avoid using fields like the date or location to reduce clutter when comparing entries.
    • Without enough fields to make a distinction between records, or with fields that are too general, you'll get a lot of duplicate results.
    Advertisement
8

Select additional fields to view.

  1. For example, an Order ID field will help you determine if the same name twice are separate entries. Include at least one field to help make this distinction and prevent accidental data loss.
9

Create the query.

  1. Click "Finish" to see the results.
    Advertisement
10

Review your results carefully.

  1. Go through each of the results and use the knowledge you have about your company to decide if the entry is a duplicate. Make absolutely sure that a record is a duplicate before you remove it.
    • If you can't decide, recreate the query with an additional field to help you make your decision.
11

Delete duplicate records.

  1. You can select multiple records to delete them all at once. [3]
    • You may want to merge some data from one of the duplicate records into the record you plan to keep.
    • Make sure to not delete all of the records that appear on the duplicate result list, or you won't have an original record left.
    Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit

      Advertisement

      Video

      You Might Also Like

      Change Your Cameos Selfie in Snapchat: 2 Simple Methods
      How to
      Log in to WeChat
      How to
      Recover Deleted Messages in WhatsApp
      How to
      Call Someone on a Computer
      The Beginner's Guide on How to Use Clubhouse (& Host Your Own Room)
      How to
      Copy a QR Code on Android
      How to
      Use Microsoft Whiteboard
      How to
      Use Facebook Messenger Bots
      How to
      Select Multiple Files on Windows 11
      How to
      Turn Off Notifications on PC or Mac
      How to
      Clear RAM on Mac
      Bing with ChatGPT: Beginner's Guide to Microsoft's AI Chatbot
      How to
      Create Folders in Google Docs
      How to
      Add a Host To a Facebook Event
      Advertisement

      References

      1. ↑ https://support.office.com/en-us/article/Find-and-remove-duplicate-data-438d54b9-f52a-4b82-a3e1-42a3e49c63ae?ui=en-US&rs=en-US&ad=US&fromAR=1
      2. ↑ http://www.techrepublic.com/article/eliminate-duplicate-records-with-this-built-in-access-query/
      3. ↑ https://www.techrepublic.com/article/eliminate-duplicate-records-with-this-built-in-access-query/

      About this article

      Thanks to all authors for creating a page that has been read 128,307 times.

      Is this article up to date?

      Advertisement