Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Your database is only as good as the data that's entered into it, and unfortunately we are all human, and we do make mistakes. If you've designed your table structure well, you can eliminate a lot of incorrect data, but not everything. It's almost inevitable that you'll eventually find some records entered more than once, with just a few discrepancies between the fields. Access has a find duplicates query that will allow you to compare records and identify potential repeats. Let's say it's the end of the year and it's time to clean up my data. I can run this query in my Customers table to make sure that no one created two accounts for the same entity.
I'll go to the Create ribbon and then click on the Query Wizard. Click on the Find Duplicates Query Wizard and click OK. We are going to base our query on the Customers table, so click Next. Now we have to select the fields. This is the tricky part. If you pick one that's likely to have variations like the Address Field you're less likely to find identical records, and if you picked too many fields just one discrepancy will eliminate erroneous record from the query results.
So think carefully about your data and which fields would be likely to be the same and which would help you identify duplications. For example, I want to choose City I'll move it over to the right-hand side. Since duplicates will likely be in the same town, and it's less likely to have typos. I'll also choose the WorkPhone. When I click Next, now it wants to know which fields I want to see in the results? I want to see all of them for the best comparison. So I'll click the double-headed arrow in the middle of the window to move all the fields to the right-hand side.
I'll keep the default name and click Finish. Sure enough, I have a few duplicates. Katz's Deli was entered with two different company names. A typo by two different sales reps. I probably shouldn't just delete this erroneous record. I might delete associated orders or other data. I'll need to talk to my reps and change the Customer number on previous orders. Once that's been done, I can delete the extra record and run the query again to look for additional duplicate data.
Performing this query on regular basis will help keep your data error free.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 69991 Viewers
80 Video lessons · 127401 Viewers
52 Video lessons · 62352 Viewers
59 Video lessons · 48052 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.