The Database Journal
03/10/2006
Adjusting Autonumber Increments

03/10/2006
Moving Access Data to Microsoft Excel

03/10/2006
Exporting Access Data with TransferSpreadsheet

03/10/2006
Export Access Data with VBA

03/13/2006
Data Layout in Microsoft Excel

03/17/2006
Excel Text to Number Conversion

03/21/2006
Transposing Excel Ranges

03/25/2006
Access VBA Timer Event

03/29/2006
What is MySQL?

04/02/2006
Microsoft Access Parameter Query Tutorial Video

04/06/2006
How does PHP Work?

04/10/2006
Microsoft Access VBA Tools - References

Click here to view all articles.

Database Design
Website Design
Search Engine Marketing
Home
Contact Us
About Us
Database Journal
Newsletters
Database Newsletter

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.

Figure 1

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:

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:

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:

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:

Figure 5

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

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

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:

Microsoft Access: Removing Duplicates