Find Duplicates
By Richard Rost
3 years ago
Find Duplicates and Delete Them in Access
In this Microsoft Access tutorial I'm going to teach you how to find duplicate records and delete them. I'll show you a manual process and then a different method to delete records in bulk.
Pre-Requisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Delete duplicate records, Finding and Removing Duplicate Records, Removing Duplicate Records
Intro In this video, we will talk about how to find and delete duplicate records in Microsoft Access using the Find Duplicates Wizard and other techniques. You will see how to identify duplicates based on one or more fields, review records manually, and use append queries along with unique indexes or composite keys to remove large groups of duplicate records efficiently. I'll also cover important steps like backing up your database before making changes and discuss some tips for handling more complex duplicate scenarios.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to talk about how to find duplicate records and delete them. There are lots of different ways to find duplicate records. In fact, I've got a couple of different videos myself. In this one, for example, I show you how to highlight them with conditional formatting. That's one way to do it.
But there's a little-known trick you can use. It's a little wizard called the Find Duplicates Wizard, and it's a nice, easy, fast way to locate duplicate records in one table. Then I'll show you my trick for deleting them in bulk.
Before we get started, before deleting anything, make sure you back up your database. Back it up. I don't want to hear any complaints. Did I know I did this? We were excited and I deleted a minute. No, no. Back it up right now. Every night. If you don't know how to back up your database, go watch this.
Here I am in my TechHelp free template. This is a free database. You can grab a copy from my website if you want to. One thing I'm going to say up front is that deduplicating your database, finding and removing duplicate records, is more of an art than a science. There are so many nuances to this. I spend many, many hours of my lessons covering topics related to this. I'll give you some links at the end of the class. But there's no easy one way to get rid of duplicate records.
For example, let's take a look at the customer table here. Let's say you've got your customers and you've got people adding more stuff. So let's say another person adds in Alex Lives, and maybe he's got multiple addresses. Alex 2. That [email protected]. That's going to be sometimes easy to find because of the first and last name, but what if it's Joe Smith? Now, they might have 15 Joe Smiths in here and they're all valid, different customers. You've got Mr. Spock up here, but someone might come in here and put Mr. Spock. Very hard to find that.
So there are some tricks we can play. There are some things we can do. You can look at multiple fields if you want to. Maybe you want to look at a combination of first name, last name, and state. That's a good way to catch if these are duplicate values.
Let me just put a couple of simple duplicates in here. I'll put another Jane Cobb and I'll duplicate myself too. Richard Rost. See, I almost typed in Rick. You could Rick, Richard, Dick. Jim Kirk as opposed to James Kirk. But to keep things easy, I'll make this James too. I'm pretty sure he's James up top here. Yeah, okay.
So let's try to find the duplicate values. Let's just look at first name only right now. I'm going to close the table. We're going to go to Create, Query Wizard. I don't use a lot of wizards, but this is one of them. I like the Find Duplicates Wizard. It's a nice, easy wizard to use.
Hit OK. It makes it easier for beginners to find duplicates without having to know any SQL. What table are you looking in? The customer table. What field might contain duplicate information? Just start with one field for now. Let's do first name. Double-click on it to bring it over. Next, what other fields do you want to see in addition to that field you're checking for duplicates? Because it gives you a little more context. Maybe you want to see the customer ID, the last name, the email address, the state, so you can tell who this is, if it's totally a different person or not. Next. What name do you want to give it? That's fine. We're going to kill it anyway. It's not a valid name.
Here is your query. Keep in mind, this is a live query. This is live data. So if you want to, you can go down this list and prune it manually, and you will have to do this sometimes. Just looking at it here, this is the same Alex Lives, and we've got two records for him. I can delete that one. Just select it and delete it. You're going to have to do that from time to time.
A lot of times you'll see you've got three Misters here. There's a Mr. Data, Mr. Worf, Mr. Spock. Those are fine. This is where it becomes handy to do this with two fields, two or more fields sometimes. Again, it's not perfect. Let's delete this one.
Go back into the Query Wizard, Find Duplicates, Customer. This time, we're going to bring in first name and last name. What other related information do you want to see? We'll do that and that, and bring in the state next, and finish.
Now it's checking for duplicates on both of those fields together. They have to match. First name and last name have to match for it to be a duplicate. Now, this shows you definitely a better circumstance here for which ones are duplicated.
Again, at this point, if you've got a small set of records, it might be a little bit worthwhile to go through and delete them manually. If you've got tens of thousands of records and you're sure they're duplicates, I'm going to show you my trick for getting rid of them all. You only do this in a situation where maybe you've got your main customer table, you go to a trade show, you import a bunch of records, and then maybe you accidentally import the same records twice.
You haven't set something like, I recommend setting fields like email address as a not necessarily a primary key, but definitely as a key field, and you index it and set no duplicates. But let's say you haven't done that. Let's say you know you just accidentally imported the same records twice. You've got a whole bunch of duplicate first names and last names.
How can we prevent that? How can we go now and prune all of those duplicated first names and last names out? Let me show you how to do it first with just one field. Let's say you've got email address and you've imported all these records. I'm going to copy and paste these email addresses.
See, this is what you should have. I've got email address set up in my table as no duplicate values, and that would prevent this problem. But let's say hypothetically you didn't do that. So I'm going to go in the customer table. I'm going to go to the email address field, and see it said yes, no duplicates. Let's just set that to no for now. Now I can have duplicated email addresses.
Let's just copy, paste, copy, paste, copy, paste. Now, I go to a trade show. I accidentally import the same data twice. I do a Find Duplicates record. There they are. Now I need to go through. I've got 20,000 of these. I don't want to do them by hand. How can I get rid of the duplicated email addresses?
What we're going to do is take CustomerT. We're going to copy it (Ctrl+C), and then paste it (Ctrl+V), and paste the structure only. So now we've got a copy of my customer table right here. It's empty, though.
Now in this table, go to your email field and set this to indexed, no duplicates. Save it. Close it. Now we're going to make an append query to copy the records from this table to that table.
If you've never done an append query before, pause the video now and go watch this guy. That'll teach you all about append queries.
Now I can go to Create, Query Design. I'm going to bring in records from my customer table and I'm going to append them into the copy of customer table. Don't worry about the name. We're going to change it in a minute. Hit OK.
Bring down the star (*). Pull over all the fields and then run it. You'll get this message: Can't append all the records. Right here it says Didn't add three records due to key violations. That means three records violated your key rules. You got three duplicates, which is what we expected. We had three records with the same email address. Are you sure you want to continue? Yes. Go ahead and finish it up.
Now if you open up Copy of CustomerT, there they are. You'll notice you don't have any duplicate records in here. Now, that works fine if you've got one field that you can do that with.
But what if you have that two-field situation going on? Let me delete this guy. Let me back up a step. Now what you do if you're happy with those results, delete your customer table and then just rename this guy CustomerT. As long as you don't have global relationships, you can get away with that. I almost never use global relationships, so you can copy and rename these tables. If you do have global system relationships, you'll have to delete those and recreate them again. That's a pain in the butt, one of the reasons I don't use them.
Let's get rid of this guy again, delete. Here's our Find Duplicates again. Remember, the original CustomerT hasn't changed. Ignore the fact that we've got the email address in here. Let's say we imported a bunch of names, and you have to do it based on first name and last name. You want to make sure those two fields are unique in your new table.
What you can do is set up something called a composite key. That's where you make a key with no duplicates based on multiple fields. In this video, I teach about composite keys. I show you how to set them up to prevent this problem from happening in the first place. Because if you have your table set up like this with the proper composite keys, then you can't import these records that are duplicates this way.
If you want to have a unique first name, last name, state, or first name, last name, street address, or whatever, that's possible.
Now, what we'll do is again copy the customer table, copy, paste, structure only. Hit OK. We're going to come into the design of this guy. This time, we can't just set a key for one field. We have to go up here to Indexes and we're going to set up a composite key.
You can call it MyKey or whatever you want to call it. You put the fields in here, first name, last name. Right here, Is Unique is Yes. That makes it so this combination of fields has to be unique. You can't have two records in the database with the same first name and last name. Again, go watch the composite key video for more information.
Save this. Now let's make that query again. Query Design, bring in records from CustomerT, append them into Copy of CustomerT, bring down that star, run it. Same thing happens. Three records due to key violations, because you got three people who have the same first name and last name. Of course, first name and last name, you're going to pick whatever fields you want to make sure that they're unique.
Go ahead and do it. There you go. Close that. Close this. You don't have to save it unless you plan on doing it again, which you shouldn't. You can delete that guy.
Now we will delete our old CustomerT. Right-click, delete. Are you sure? Yes. Goodbye. Then we'll rename this guy to just CustomerT. There you go. There are all your customers that have been cleaned, for the most part.
Like I said, depending on the state of your data, this is not an easy procedure. This could take hours for you to properly go through and dedupe your database.
If you want to learn more, in Access Expert Level 18, I talk about the Find Duplicates query in more detail, and we go through some other stuff too, like Find Unmatched queries. We go through some advanced query stuff. There's lots to do with queries in this one.
If you really want to get serious about this deleting duplicate data stuff, I have a whole section on scrubbing data. Now, scrubbing doesn't mean get rid of it. It means cleaning your tables out like scrubbing them with a sponge. I'm going to go through a lot of different stuff with how to make sure you've got your table set up properly. We'll use record sets and all kinds of cool stuff.
Hope that gives you some insight into how to find and delete duplicate data in your database. I hope you learned something today, and I'll see you next time.Quiz Q1. What is the main purpose of the Find Duplicates Wizard in Microsoft Access as discussed in the video?
A. To create automatic backup copies of your tables
B. To help locate duplicate records in a table with ease
C. To generate complex SQL queries from scratch
D. To format table layouts for easier reading
Q2. Before attempting to delete any duplicates, what is strongly recommended?
A. Upgrading Microsoft Access to the latest version
B. Backing up your database
C. Creating a new database from scratch
D. Disabling all database relationships
Q3. Why is deduplicating a database described as more of an art than a science?
A. Because there is a single universal method for all databases
B. Because it is random and unpredictable
C. Because there are many nuances and no single best way to do it
D. Because it always requires third-party tools
Q4. If you are only concerned with one field, such as email address, how can you prevent duplicate entries in your table?
A. By setting the email field as indexed (no duplicates)
B. By deleting and recreating the email field regularly
C. By hiding the email field
D. By creating a backup of the email field
Q5. What is a composite key used for?
A. To combine multiple databases into a single file
B. To sort data based on one field
C. To enforce uniqueness across multiple fields together in a table
D. To assign colors to records based on their status
Q6. What should you do after successfully moving deduplicated records to a new table?
A. Delete the original table and rename the new table to the original's name
B. Leave both tables in the database indefinitely
C. Export both tables to Excel
D. Make both tables read-only
Q7. When would you want to use the manual method of deleting duplicates, as shown in the video?
A. When you have tens of thousands of records
B. When you are only sure there are a few duplicates and the set is small
C. When all records are identical
D. When using SharePoint integrations
Q8. What does it mean when you receive a key violation message while running an append query?
A. The table is corrupted
B. The query ran out of available memory
C. Some records violated unique field constraints (duplicates found)
D. The query was canceled by the user
Q9. What does scrubbing data mean in the context of the video?
A. Deleting all records in a table
B. Cleaning and ensuring your data and tables are properly set up
C. Adding additional tables to your database
D. Encrypting all database contents
Q10. What should you do if your table has global system relationships before copying and renaming tables to remove duplicates?
A. Ignore them; they do not affect anything
B. Delete and recreate these relationships after the deduplication process
C. Convert tables to queries first
D. Use macros instead
Answers: 1-B; 2-B; 3-C; 4-A; 5-C; 6-A; 7-B; 8-C; 9-B; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone is all about finding and deleting duplicate records in your Microsoft Access database. This is a common task, and there are several different ways to approach it. I have covered techniques such as using conditional formatting to highlight duplicates in previous videos, but in this session, I want to introduce you to the Find Duplicates Wizard, which is a quick and user-friendly way to locate duplicates within a single table. I will also share my method for deleting these duplicates efficiently.
Before we begin any process that involves deleting data, make absolutely sure you have a backup of your database. It is easy to make mistakes, and you do not want to lose valuable information. If you are not sure how to back up your database, take a moment to learn how before moving forward.
Deduplicating your database can be more of an art than a science. There are many variables and nuances involved, and while I go into much more detail in my comprehensive courses, today I will share some of the common concepts you need to know.
Let's consider a sample customer table. Sometimes, duplicates are obvious, like two records for Alex Lives with similar information. However, you may have cases like Joe Smith, where many individuals legitimately share the same name, making duplicates much harder to recognize. The key is to be able to look at a combination of fields—such as first name, last name, and perhaps a state or other identifying data—to more reliably identify unwanted duplicates.
After entering some sample duplicates, you can start the process by using the Find Duplicates Query Wizard. Although I typically avoid most wizards in Access, this is one I do recommend, especially for beginners. The wizard allows you to select the table you wish to check, choose the field or fields that may contain duplicate information, and then specify additional fields to display in the results for context. For example, you might want to check duplicates on first name alone at first, but then also display the last name, email, or other fields to help you decide if records are truly duplicates or simply similar.
The wizard produces a live query showing the potential duplicates. From here, for smaller datasets, you can manually review and remove duplicates as needed. When you have many records or are certain that an import process has introduced unwanted duplicates, you may want to automate the deletion of duplicates.
One good practice is to use fields like email address as a unique or at least indexed field, set to not allow duplicates. However, if this was not configured ahead of time, and you now find yourself with a batch of duplicate records (such as after an accidental double import), there's a process for efficiently clearing them out.
Here's how I handle it: first, copy your main table structure and paste it as a new blank table. Within the design of this new table, set the field or fields on which you want to enforce uniqueness (like email address) to be indexed with no duplicates allowed. Then, create an append query to copy all records from the original table into the new one. Access will automatically prevent duplicate records from being copied over, based on the key fields you have set. For example, if you have three records with the same email address, only one will appear in the new table after the append query.
Once you're satisfied, you can delete the original table and rename the clean copy to the original table's name. As long as you do not have global relationships in place, this is a straightforward process. If you do use global relationships, you may need to recreate them after switching tables, which is a bit more involved and one of the reasons I tend to avoid them.
When it comes to duplicates involving more than one field—say, both first name and last name—you would need to set up a composite key. This involves creating an index that combines both fields and requires their combined values to be unique. After copying your table design and setting up this composite key, the same append query process will only allow unique combinations of those fields into the new table.
This approach works for whatever combination of fields suits your particular situation. For more advanced cases, you can create composite keys covering three or more fields, such as first name, last name, and state.
I want to emphasize that cleaning up duplicate data can be a complex task, requiring careful attention to detail. Depending on how your data is stored, the process can take a significant amount of time to perfect.
If you are interested in exploring this topic further, I cover Find Duplicates queries and other advanced techniques, such as Find Unmatched queries, in Access Expert Level 18. For a comprehensive approach to 'scrubbing' your data—meaning to clean and optimize your tables for reliability and efficiency—I have dedicated sections in my curriculum that detail these methods using tools like record sets and proper table structure.
I hope this overview has helped you understand how to find and remove duplicate records in Access. To see step-by-step instructions and a full demonstration of these techniques, you can find a complete video tutorial on my website at the link below. Live long and prosper, my friends.Topic List Finding duplicate records with the Find Duplicates Wizard
Selecting duplicate criteria using one or more fields
Viewing and analyzing duplicate records in queries
Manually deleting duplicate records from query results
Using append queries to remove duplicates in bulk
Copying tables and pasting structure only
Setting indexed fields to prevent duplicate values
Creating a composite key to enforce uniqueness
Handling duplicate records across multiple fields
Renaming and replacing original tables after deduplication |
