delete duplicates?
- Thread starter wh00t
- Start date
D
DJN
Guest
You mean other than going through the table manually!
Well, here is the Microsoft method.
Make a copy of the structure of the table from which you want to remove the duplicate records.
To copy a table:
Select the table in the Database window
On the Edit menu, click Copy.
On the Edit menu, click Paste.
Enter a name for the new table.
Select Structure Only
Click OK.
Open the new table in Design view.
Select the field(s) that contain the duplicate values.
To make your selection the primary key for the table, click the Primary Key button on the toolbar.
Save and close the table.
Create an append query based on the original table containing duplicates.
In the query Design View, on the Query menu, click Append Query.
In the Append dialog box, click the name of the new table from the Table Name list, and then click OK.
Include all the fields from the original table by dragging the asterick (*) to the query design grid.
On the Query menu, click Run.
Click Yes in the dialog box advising you that you are about to append records.
Because the Primary Key field(s) in the new table will not accept duplicate values, you will get an error message, depending on the version of Access you are using abou Access unable to append the records.
Ignore the error message and run the query. View the contents of the new table. When you're sure the new table has the correct unique records, you can delete the original table, and then rename the new table using the name of the original table.
David
Well, here is the Microsoft method.
Make a copy of the structure of the table from which you want to remove the duplicate records.
To copy a table:
Select the table in the Database window
On the Edit menu, click Copy.
On the Edit menu, click Paste.
Enter a name for the new table.
Select Structure Only
Click OK.
Open the new table in Design view.
Select the field(s) that contain the duplicate values.
To make your selection the primary key for the table, click the Primary Key button on the toolbar.
Save and close the table.
Create an append query based on the original table containing duplicates.
In the query Design View, on the Query menu, click Append Query.
In the Append dialog box, click the name of the new table from the Table Name list, and then click OK.
Include all the fields from the original table by dragging the asterick (*) to the query design grid.
On the Query menu, click Run.
Click Yes in the dialog box advising you that you are about to append records.
Because the Primary Key field(s) in the new table will not accept duplicate values, you will get an error message, depending on the version of Access you are using abou Access unable to append the records.
Ignore the error message and run the query. View the contents of the new table. When you're sure the new table has the correct unique records, you can delete the original table, and then rename the new table using the name of the original table.
David
The way that I delete duplicates is to copy the structure only of the table and then set the primary key so that no duplicates are allowed. Then I append the data from the old table to the new table. There may be an easier way but this always works for me!
HTH
Val
HTH
Val
