How to remove duplicate data or records in Microsoft Access

If you are going to talk about programs that allow you to manage a database and create queries and forms , it is most likely that Acces will shine. Because it contains a huge number of functions that make it very complete. That is why today you will learn how to eliminate duplicate data or records in Access, in a few minutes.

And, so that you can use this program to the full, you need to keep in mind the simplest things first, such as deleting data , because this way you can gradually increase your level to be able to move on to more complex processes.

How to remove duplicate data or records in Access?

To go straight to the point that interests you and solve as soon as possible the initial question of  How to eliminate duplicate data or records in Access ?, you have to know that there are two ways to perform this process, one is manual and the other automatic (before starting take into account that to understand each step, you have to know how to join or combine the Microsoft Access databases , if not, you may not know the buttons are unknown to you).

The one you will see first today will be the manual. If you want to achieve this you have to go directly to the Access program and open it, then you have to go to the database where you want to eliminate duplicate elements.

Once there, click on the tab called “Queries” and then press the “New” button. This action will allow you to get to another section where you must select “Query wizards to find duplicates” and hit “Ok” to execute it.

 

When you are in the wizard, select the radio button that will have the name “Tables”, in it, choose the table you want to observe and click “Next”, then select each field where you want to search for duplicates and click “Next ”.

Finally, choose an additional field that you want to observe (that is optional), and then enter a name for the query, or use the one that comes by default, when finished press the ” Finish ” button .

Now you will find yourself in a section where you can see the duplicates and delete them manually, for that you just have to click on the row where they are and press the delete button on the keyboard. The time you take will be proportional to the number of duplicates you have.

Remove duplicates automatically

With what you saw above, you already know how to get rid of duplicates, so the question How to eliminate duplicate data or records in Access? Is already solved, what you will see next is a method that allows you to do the same but automatically.

To start with this you have to go to the beginning of the Access program, and from there select the option ” Tables “, look for the table that has the duplicates and hit the key combination Ctrl + C to copy it.

Then and immediately select Ctrl + V, to paste the copied and create a new table , choose the name you want for it and select the option “Structure only”, then click “OK” to confirm. When you have it, choose it with the mouse and press “Design View”.

 

Then choose the field where you do not want there to be more duplicates within your table, and right click on the gray box that will appear on the left, in the list of options that will appear press “Primary key” (thanks to said key will remove duplicates as they must be unique).

At the end of this process, go to “Queries” and select ” Design View “, then choose the table created and click “Append” as the query you will do (to find this option you have to press the icon of two white boxes that are next to the red exclamation mark).

Finally and to finish with the answer to the question How to eliminate duplicate data or records in Access? , you have to drag the * to the top of the list of fields and hit the red exclamation mark that appears above, so that only unique records and no duplicates will be pasted into the new table.

Create a small backup

As a final trick you should create a kind of backup by renaming the original table and leaving it there, just in case some of the duplicates actually weren’t. You can name it like “original table”. 

As a final tip, in case you want to delete more data (which is not necessarily duplicates), you can choose to delete and cascade update related records .

 

Leave a Comment