Removing Duplicates from Your Access Database
and Determining Which Were Removed
~~James
La Borde
There are times when you need to remove
duplicate records from a table. It is easily done in Access, the only
drawback is that you don't control which duplicates are removed. If this may
cause a problem, you can determine which records were removed so that you
can ensure the correct records are kept.
The first step is to create a duplicate of your original table. To do this
simply right click your table in the Tables window and click Copy.
Click Paste. This will bring up a new dialog box. Enter a new table name
in the appropriate area and change the Paste Options to Structure Only.
If your Original table does not have an auto-number field, add one. In
the newly copied table, if the original table did not have an auto-number
field, add a number field with the same name as the new one in the original
table. If it did, change the auto-number to a number field in the new table.
You will also need to determine your primary key and mark it as such (the
field with the duplicates).
You will now create an append query using your original table as the
source and the New Copy as your destination.
Once this is ready, execute the query. It will run through and see what
is due to be appended then come back with a warning message that not all
records can be appended. It should indicate that a number of the records
could not be appended due to primary key violations.
This is expected. Simply click 'Yes' to allow it to append the rest of
the records. It has now removed all of your duplicates from the new table.
Now however, maybe you would like to determine what was removed. This is why
we added the auto-number/number field. Create a new query, and bring in both
the original and copied tables. Access will add a link between them on the
primary key automatically. Delete this. Drag the auto-number field from the
original and drop it on the related number field in the new table. This will
create a new join. Double click this join to bring up the 'Join Properties'
dialog box. Change the properties to include all records from the original
table and only those that match in the new table.
Add all of the fields you would like to check from the original table.
Then add a field from the new table. This field need not be displayed, but
you need to set the criteria of this field to 'Is Null'.
Execute this query and you will have a result-set that is all of the
deleted records.
In summary, follow these steps to accomplish this task.
1. Right Click and copy your table
2. Right Click and paste the table, rename it and check the selection for
Structure Only
3. If you do not already have an auto-number field, add one to your original
table and add an additional field to your new table as a Number - Long
Integer data type
a. If you have an auto-number field in your original table, change it to a
number - Long Integer field on the new table
4. Run your append query and click 'Yes' when it tells you fields will not
be added.
5. Open a new query and bring in both your original table and the new one.
6. Link the fields on your auto-number/Number - Long Integer field.
7. Double click the link between the tables. This should bring up a dialog
box that allows you to change the link type. By default, it will be set to
include fields that are the same in both tables. Select the option that
includes all your records from your original table and only matching records
from your new table.
8. Bring in all the pertinent fields from your original table and the number
field from the new table. Uncheck the box to display the number field and in
the criteria location for this field type, Is Null.
9. Execute the query.
10. Your results will be all of the records in your original table that are
not in your new table as their primary key fields are duplicated within the
table.
<<<back to contents
James La Borde works
in the computer department at a Credit Union,
where he uses Access, SQL Server, VBA, and ODBC daily. He also
teaches online Access classes at
Eclectic
Academy.
,
