3
readers
helped!
How to Find Duplicates Easily in Microsoft Access
Explore this Article
IN THIS ARTICLE
Other Sections
X
wikiHow is a “wiki,” similar to Wikipedia, which means that many of our articles are co-written by multiple authors. To create this article, 18 people, some anonymous, worked to edit and improve it over time.
The wikiHow Tech Team also followed the article's instructions and verified that they work.
This article has been viewed 127,685 times.
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.
Steps
1
Know what makes data "duplicate".
-
Duplicate data doesn't mean that all of the fields are identical. 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.
-
It is recommended that you create a new backup before making big changes. 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.
-
In order to avoid data conflicts, make sure no users will be adding data to the database. 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.
-
The Query tool can find entries that contain duplicate content. The process for starting the wizard varies depending on the version of Access you are using: [1] X Research source
- 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.
6
Choose the table you want to search.
7
Select the fields you think contain duplicates.
-
Select all of the fields that you want to compare for duplicate data. 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] X Research source
- 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.
9
Create the query.
10
Review your results carefully.
-
Any potential duplicates based on your criteria will be displayed. 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.
-
Right-click on the left column and select "Delete Record" to remove a duplicate. You can select multiple records to delete them all at once. [3] X Research source
- 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
How to
Lock the Gallery on Samsung Galaxy
How to
Add a New Post in WordPress
How to
Copy a QR Code on Android
How to
Store and Read Documents on an iPhone
How to
Use Facebook Messenger Bots
3 Ways to Get More Effects on TikTok
How to
Use Samsung Pay
How to
Tell Who Has Viewed Your Instagram Story on Android
How to
Turn Off Notifications on PC or Mac
How to
Download Images from Google on PC or Mac
How to
Download a Torrent With Android
How to
Tell if Your Snapchat Message Was Read
How to
Bump a Post on Facebook
How to
Use Utorrent on an Android
Advertisement
References
- ↑ 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
- ↑ http://www.techrepublic.com/article/eliminate-duplicate-records-with-this-built-in-access-query/
- ↑ https://www.techrepublic.com/article/eliminate-duplicate-records-with-this-built-in-access-query/
About this article
Tested by:
wikiHow Technology Team
wikiHow is a “wiki,” similar to Wikipedia, which means that many of our articles are co-written by multiple authors. To create this article, 18 people, some anonymous, worked to edit and improve it over time. This article has been viewed 127,685 times.
How helpful is this?
Co-authors: 18
Updated: January 31, 2023
Views: 127,685
Thanks to all authors for creating a page that has been read 127,685 times.
Is this article up to date?
Advertisement
Tested by:
wikiHow Technology Team
Click a star to vote
% of people told us that this article helped them.
Co-authors: 18
Updated: January 31, 2023
Views: 127,685
You Might Also Like
How to
Lock the Gallery on Samsung Galaxy
How to
Add a New Post in WordPress
How to
Copy a QR Code on Android
How to
Store and Read Documents on an iPhone
