How Access handles duplicate records

access| Repeat | repeat record
There are duplicate records in one table, the number of repetitions may be one or more, how to leave only one in the duplicate records, delete other redundant records, so that each record of the dataset is unique? This article uses a relatively clumsy but logical more clear method, hope that we can provide a better way!

1 list duplicate records in a table

(Sameoda)

SELECT [2]. [prefecture-level], COUNT (*) as record number from 2GROUP by [2]. Cities Having Count (*) >1order by [prefecture-level];

Record number of prefecture-level cities 100000951094210000095111621000009511272100000951138210000095114921000009511502

2 The following is the process of removing the same record

1 List all duplicate records in the table

(Duplicate records in table 2)

SELECT 2.*from 2 Right JOIN Sameoda on [2]. [prefecture-level]=[sameoda]. [prefecture-level];

ID Number prefecture-level insurance goods name quantity and packing unit declaration value (yuan) transport from transport to 74691466100000951094 benzene bezafibrate tablets, silymarin capsules 3 cases 18600 Tianjin Jiamusi 53511077100000951094 benzene bezafibrate tablets, Water Silymarin Capsules 3 cases 18600 Tianjin Jiamusi 53501076100000951116 Water Silymarin Capsules 5 cases 29000 Tianjin Urumqi 74681465100000951116 Water silymarin Capsule 5 case 29000 Tianjin Urumqi 53531079100000951127 fluoro Amine Slice 4 Box 66800 Tianjin Harbin 74711468100000951127 fluoro Amine Film 4 case 66800 Tianjin Harbin

2 find only one record from the duplicate record

Minbihanhao

In the SELECT 2.*from 2 left JOIN table 2, Duplicate records on [2]. [Duplicate records in id]=[table 2]. [ID] WHERE [Duplicate records in table 2]. [id] is Null;

Prefecture-level id100000951094535110000095111653501000009511275353100000951138535410000095114953521000009511505355

3 Display all information of a unique record

Allsame1

SELECT 2.*from 2 left JOIN Minbihanhao on ([2].[ Prefecture-level]=[minbihanhao]. [prefecture-level]) and ([2].[ Id]=[minbihanhao]. [ID]) WHERE [Minbihanhao]. Cities is isn't nullorder by [2]. [ID];



4 Remove all the same records in the original table

Dropsame2

In the SELECT 2.*from 2 left JOIN table 2, Duplicate records on [2]. [Duplicate records in id]=[table 2]. [ID] WHERE [Duplicate records in table 2]. [id] is Null;

5) Table 2 Remove duplicate records = Drop all the same records in the original table + all information showing unique records

Table 2 removing duplicate records

Select *from dropsame2 UNION select * from allsame1;