In Access when we manage a large number of records and perform various operations, it is possible to unintentionally find duplicate data.
We at Soultricks today explain, in a few steps, how locate and delete duplicate records:
Open your Access database (from version 2007 onwards) and click on the icon Query Wizard of the Create group. In the window that opens, select Duplicate Search Query Wizard and click OK.
At this point, select your source table or query in which you want to locate duplicate records and click on Forward.
Now identify among the available fields of the chosen table, which are the fields in which you can easily identify the duplicate values (for example: Amount, date, name, etc.) and bring them to the right of the list (you can choose up to a maximum of 10 fields) and click Next.
In the next screen of the PopUp you can choose other fields to be displayed in the final query, our advice is to take them all and move them to the right of the list. Click NEXT, give a name to the Query (Ex. Query_with_Duplicates) that will be created and click on end.
At this point open the query created and you will see all the duplicates clearly visible. Now are you wondering how to delete duplicates and keep non-duplicate records instead?
Here it is explained in 3 steps:
1) Create a new query (Create - Query Structure) and select from Show Table the duplicate query. Select all fields and group them by clicking on the icon TOTAL. Click on the icon at the top Table Creation and give a name to the new table (ex. Record_not_duplicated) that you are creating with the records grouped and not duplicated ed Run the Query.
2) Now all you have to do is delete all data present in the Query, that call Query with Duplicates; open it and select the whole table, and press Delete.
3) Do Create - Query Structure - Show Table, select the table called Record_non_Duplicati and do Add. Then click on the icon Queuing, in the window that opens, select the source table and confirm.
Run the Query and finally your origin table will be freed from the many duplicate records.
