asked on

Duplicate Record Identification and Deletion for MS Access

I am responsible for a database of approximately 750,000 customer records. Using simple duplicate checking routines, I identify approximately 32,000 duplicate records. More if I use Soundex and possibly even more if I were to parse the address string into various elements. And still more when considering misspellings and middle initials appended to first names.

My question - Are any of you aware of any routines developed in Access VP that could assist me in this task. Not only would I like to identify duplicate records, but I would like to automatically delete/consolidate records that were found to be duplicates.

Thanks for the help.

SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Douglas Cummings

ASKER

Thanks for the comments. My situation is a little more complicated than I mentioned. I can't use the built in duplication routines because I need to consolidate as many as 20 child tables from the eliminated record into the retained record. And I need to apply certain rules to determine which record to keep and which to eliminate. I am hoping to do this with Access VB. What I am looking for is some routines to help me resolve name spelling issues as well as non-standard address formats, i.e., 101 W Main Street vs. 101 West Main vs. 101 West Main ST # 102.

The data is stored in MS SQL 2008 R2 databases and the front end is Access - currently 2003, but soon to be 2010.

I wish we could get users to standardize on data input formats and I have built in various traps to catch errors. But it seems that I am not as adept at anticipating their mistakes as they are in making them.

Thanks again.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Douglas Cummings

ASKER

Sorry that I haven't revisited this since it was originally posted. Everyone's comments were helpful, but I decided to use the third party solution suggested by Pat Hartman. Thanks to everyone that responded to my issue.

PatHartman

Let us know how it works for you so we know whether or not to recommend it in the future.