Microsoft Access: Removing Duplicates
If you need to remove duplicate records in Microsoft Access, there's a quick and easy way to do so without needing a query or any VBA Code. As a matter of fact, with just a little bit of copying and pasting, you can achieve the same result as you would through a different and more complicated method.
Duplicate records present a real problem for database administrators. If you have duplicate records in your database, there is a great possibility that poor relational database design is to blame. These duplicates can bloat the size of your database, skew statistics, and can be general obstacles to completing your database tasks efficiently.
The reasons for deleting duplicates can vary greatly. It's possible that this data could've been imported from an external source, and thus you need to eliminate duplicate records, or that the database was initially designed improperly. Whatever the reason, removing duplicate records is a fairly common task in the world of database administration - particularly if you handle imported data from many different sources.
In Figure 1, we've got four records, one of which is a duplicate - for Sarah Smith. In this case, we really don't care which of the two records we keep because they both contain the same data.
In order to strip duplicates from this table, we'll need make sure the table is closed, and highlight the table in the database window. Then click Edit, and Copy.
After we copy the table, click Edit again, and then Paste. We get the dialog box shown in Figure 2:
Select "Structure Only" in Paste Options and set the table name to something different. Now go into your new table, and set the key field to whichever field you use to determine duplicates. In the case of this example, we'll be setting the field "SSN" to a primary key field because that's the field we'll be using to determine what a duplicate record is (in this case, a record with the same Social Security Number).
The next step is to once again copy the original table (the one containing duplicates), by highlighting it, and clicking Edit, then Copy. Then click Edit and Paste again, except this time, we're going to adjust the Paste dialog box to that shown in Figure 3:
Now, we've copied the original table (containing the duplicate records), and we're appending only the data into the table that we created and adjusted the key fields for. When we do this, we'll get an error message like the one in Figure 4:
Essentially, Access is telling us that if we go ahead and append these records into the new table, we're going to lose one because of a key violation. Well, that's exactly what we want. We say yes to append, open the new table and voila, only unique records:
And there you have it. In a nutshell, we're copying the table, and pasting it's structure. Then we change the structure with a key field so duplicates can't be contained within. Then we paste the old table into the new table and our duplicates are automatically removed. At this point, you can remove the old table all together, save it for archive purposes, and rename your new table or once again adjust the key fields in it if need be.
There are many ways to remove duplicate records from tables, though you can't argue with the simplicity of a technique that only requires you copy and paste a table twice - no code, no queries, and the end result is exactly the same!
Good luck implementing this technique in your own databases - as with every technique where you are messing with records, you'll want to back up your database before fooling around with large amounts of data. I hope this small article has been helpful!
©2014 Blue Moose Technology, LLC
Article Publishing and Re-Print Guidelines
- The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
- When re-published online, all links must be live hyperlinks.
- These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
- These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!
You can easily link to this article!
If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:
On your page, the code will appear like this: