Removing Duplicate Records In Microsoft Access

Published by Jacob Martin on October 20, 2014

Do you want to remove duplicate records from Microsoft Access then you are at right place. Here you will learn quick and easy way to eliminate duplicate record without using any query or VBA Code. With a simple copying and pasting, you can get the exact result as you will through a complicated and different method.

For a database administrator duplicate records are really a big problem. If you got duplicate records in your database then there is a possibility which a poor relational database design is to blame. These duplicate records can inflate the size of your database, twist the statistics, and can be a general problem for completing your database tasks well.

The reason behind the deletion of duplicates can vary at every stage. It can be that you cannot import the data from external source and so you need to remove the duplicate records. No matter what are the reasons behind removing duplicate records it’s a common task.

In Figure 1, You can see four record one of is duplicate – for Alva Christeen. In this situation we don’t know which of the two records should be kept as both have same data.

Figure 1

To duplicates from this table, you need to make sure that the table is closed, and highlight the table in database window. After this click Edit, and Copy.

After copying the table, hit Edit again, and then Paste. You will get the dialog box as shown in Figure 2:

Figure 2

Choose “Structure Only” in Paste Options and then set the table name with something different. Now move into your new table, and set key field to that field you want to decide duplicates. In this situation here we have set the field “SSN” to primary key field as that’s the field will be used to conclude what duplicate record is ( in this case the record with the same Social Security Number).

Next step is to copy the original table once again (one containing duplicates), by highlighting it, and click Edit, then Copy. Now click Edit and Paste again, this time adjust the Paste dialog box as shown in Figure 3:

Figure 3

Now after copying the original table (containing duplicate records), append only the data into table which you have created and adjusted the key fields for. After doing this you will get an error message as shown in Figure 4:

Figure 4

Basically, Access tell us that if you move ahead and append these records in the new table then you will lose one as of key violation. Well that is what we want so just say yes to append, open new table and voila, only the unique records:

Figure 5

And you have it. Copy the table and paste its structure. And you can change the structure with key field so that the duplicate can’t be contained inside. Now paste the old table in the new table and duplicates will automatically removed.  At this time you can remove the old table all at once and rename the new table or again adjust key fields in it if you need.

There are many ways to remove the duplicate records from tables, so you can argue on the simple way.

133 Total Views 2 Views Today