How to create duplicate search queries in Access 2016

Duplicate search queries allow you to search and identify duplicate records in a table or multiple tables. Duplicate records are a record that refers to the same or the same person as another record.

Not all records that contain similar information are duplicate content. For example, records of two orders placed on different dates but containing the same items will not be duplicates. Similarly, not all duplicate records contain exactly the same information. For example, two customer records may refer to the same person but include different addresses. The record containing the old address will be a duplicate record.

Why is it important to remove duplicate records? Consider the example above. If you have multiple records for a customer, it will be difficult to view that customer's order history, as the information will be spread across a number of unrelated records. You can even deliver your order to the wrong address if the person entering the order information chooses a record that contains the old information. It's easy to visualize how a duplicate record can undermine the integrity and usefulness of the database you currently own.

Fortunately, MS Access makes it easy to find and locate potential duplicate records. Note that Access will not delete the records for you or help you find out which records contain new information. You will have to do it yourself. However, if you are familiar with data in your database, removing duplicate records will be a manageable task.

How to create duplicate search queries

1. Select the Create tab on the Ribbon, locate the Queries group and click the Query Wizard command .

How to create duplicate search queries in Access 2016 Picture 1

2. The New Query dialog box will appear. Select Find Duplicates Query Wizard from the query list, then click OK.

How to create duplicate search queries in Access 2016 Picture 2

3. Select the table you want to search for duplicate records, then click Next. The example is looking for duplicate customer records, so the author will select the Customers table .

How to create duplicate search queries in Access 2016 Picture 3

4. Select the fields you want to search for duplicate information, by selecting them and clicking the right arrow button. Please select non-identical fields in duplicate records. For example, because we are looking for duplicate customers, only select the First Name and Last Name fields because it is unlikely that many people with the same name and the same name will order at the same bakery.

5. When you have added the desired fields, click Next.

How to create duplicate search queries in Access 2016 Picture 4

6. Select additional fields to see in the query results. Select the fields, which will help you distinguish between duplicate records and choose which schools you want to keep. In the example in this article, the author will add all the fields related to the customer address, plus the Phone Number field , because the records with the same customer name may contain inaccurate information in the field. this. When you are satisfied, click Next.

How to create duplicate search queries in Access 2016 Picture 5

7. Access will suggest a name for your query, but you can enter a different name if you want. When you are satisfied with the query name, click Finish to run the query.

How to create duplicate search queries in Access 2016 Picture 6

8. If Access finds any duplicate records in your query, they will be displayed in the query results. Review the logs and delete any outdated or inaccurate records when needed.

How to create duplicate search queries in Access 2016 Picture 7

Tips for solving duplicate records

1. Save your duplicate record queries and run them regularly.
2. Find duplicate potential records by viewing linked data in other tables. You can do this by searching for the ID numbers of these records in the relevant tables. Are records linked to most old orders while another record contains recent orders? The second is likely to be a record containing newly updated information.
3. When you decide which record to delete, make sure you don't lose any information you may need. In the example in this article, before I deleted my duplicate record, the author found all orders associated with that record's ID number and replaced them with the record ID number author decided to keep.

See more:

1. Options to create data queries in Access 2016
2. Create data queries in Access 2016 from simple to complex
3. MS Access 2003 - Lesson 18: Chapter 5: Sorting and filtering information