Deleting Duplicate Records from a Table

Deleting Duplicate Records from a Table

If you are dealing with data imported from another source, you may find that you have duplicate information included in the data. Whilst Access has a wizard that will allow you to easily identify the duplicates, there is no easy way to delete any duplicated information, but leave one copy of each record in the table.

Instead of using the Access wizard to detect duplicates, the best way of doing this is to create a query that selects the unique records from the table, and then turn this into a make-table query.

Start by creating a new query, and adding the table that has the data in. Add all of the fields to the query grid (either by adding the individually, or by adding the asterisk, which means 'All Fields'). Change the query's Unique Values property to yes (note that Unique Records might appear to work, but won't if the records are only coming from one table), and when you run the query, you will only see the unique values being shown. Now convert this into a Make-Table query, and when you run it, you will have a table that has no duplicates in. The final SQL will look something like:

SELECT DISTINCT [tblName].* INTO tblUnique FROM tblName;

This same approach can be used when you have more then one table that you wish to eliminate duplicates from. Start by creating a UNION query (note that the only way to create a UNION query is to write the SQL, you cannot use either a wizard or the normal query design method). Start by creating a new query, but instead of adding tables, click on close. Then select 'SQL' from the Query toolbar. The SQL that you will be entering looks like:

SELECT * FROM tblOne
UNION SELECT * FROM tblTwo
UNION SELECT * FROM tblThree;

The above SQL selects all of the fields from tblOne, tblTwo, and tblThree. If you have different numbers of fields in the tables, or the fields are in a different order, then you will need to specify the fields as required:

SELECT Field1, Field2, Field4 FROM tblOne
UNION SELECT Field1, Field2, Field4 FROM tblTwo
UNION SELECT Field1, Field2, Field4 FROM tblThree;

One of the advantages of using the UNION query like this is that by default, it removes duplicates automatically for you. Once you have this query created, you can then create a Make-Table query based on this to give you the unique data that you require.

Top

Last modified at 06/06/2006 15:00:05