Search for:
Quick Category Links

SharePoint
Project Management
Professional Development
Office Applications
Categories
Tags
AJAX briefing Business Certification Conferences Consulting CorasWorks Document Management Education and Training Email Enterprise Project Management EPM Events Excel Knowledge Management Maintenance Meta element Microsoft Microsoft Access Microsoft Excel Microsoft Office Microsoft Outlook Microsoft PowerPoint Microsoft Project Microsoft SharePoint Microsoft SharePoint Designer Microsoft Windows Microsoft Word Mondosoft Office 2007 Ontolica Operating Systems PMI PMP PowerPoint Project Management SDPS search seminar SEO SharePoint Designer Social Media Twitter Windows Server 2008 Windows Vista
Other Links
Blog Network:
Name:
Springhouse Blog :: Our Knowledge = Your Learning
Topics:
technology, sharepoint
Join my network
Blog Networks

Meet Our Authors

Jan

Access - Removing Those Pesky Duplicate Records

By Bob S (7 posts -- read other by posts Bob S)

If you find duplicate records through a query, the next logical step is to delete them. However, the number of records to be deleted can run into the hundreds, or even more. Manual deletion of these records would be impractical because of the time involved. And you can use an Access trick to remove duplicates from any table.

If you want the newest of the duplicates to be kept, sort the table in descending order by the key field. If you want the oldest record to be kept, sort the table in ascending order by the key field. Close the table and save your changes.

Next, copy the structure of the table to a new table. Remove all existing keys.

Designate the fields that you have determined as indicators of duplicate records as key fields.

Convert the Find Duplicates query to an append query that will append all records to the new table. Run the query.

Because the fields that contain duplicate information are key fields in the new table, Access will remove the duplicate records because duplicate keys are not allowed.

Remove the existing keys, and designate the original key fields as your keys.

Delete the old table.

Rename the new table with the old table name.

For example, a table called tblCustomer with a Customer ID field as a key field contains duplicate customer information, entered by mistake. Stores in the same city have been entered twice, so the combination of Store name and City indicate duplicate records.

First, open the table tblcustomer. If you want the newest of the duplicates to be kept, sort the table in descending order by the key field. If you want the oldest record to be kept, sort the table in ascending order by the key field. Close the table and save your changes.

Copy the tblCustomer table structure to a new table called tblNewCustomer.

Open the table tblNewCustomer in design view. Remove the key designation from the Customer ID field.

In tblNewCustomer create a multi-field key by selecting the Store Name field and the City field

Create a find duplicates query, using Store name and City as the indicator fields.

Convert the Find Duplicate query to an append query and run it. It will attempt to append all the records to the table NewCustomer. The append record will fail every time an attempt is made to write a duplicate record into the table, so only one of the duplicates will be written to the new table.

After the query finishes, delete the tblCustomer table. Rename the table tblNewCustomer as tblCustomer. All duplicates have been removed.

Related Posts:

Filed Under Microsoft Access, Office |