473,714 Members | 3,933 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

------ Help Needed. How to delete similar but not identical records

Greetings All

I have requested help before and I really need your help in this. I
am working on an Access database of contacts. I have plenty of
records but plenty of them are almost duplicates. Data in some fields
are misspelled and entered as completly different records. I wonder
how could I clean the database in a way I have single record of
contact information for each contact.

Example:
Record 1

Name:Johns Smith
Tel: 734-123-4567
Address: 123 Any Street
Title: VP of Operations
Company: ACME
Record2
Name: John Smith
Tel: 734-123-4567
Address: 123 Any Street
Title: VP of Operations
Company: ACME

So, record 1 and 2 are basically identical; the name is misspelled.
If someone entered new records because names were misspelled, how can
I go and clean the data of such records.

Best Regards
A.D.B.
Nov 12 '05 #1
3 2631
I'm sorry to say that there is no straightforward , simple way to do what you
want. You may use the * "wildcard" character for
"near match" of particular kind, but for just any _near_ match, this will
not be simple. That might work nicely for your example, but it would be
difficult to match a John Smith with a Hohn Smith mistyping.

And, any way you go about it, there will be manual action required.

I saw your earlier post on the subject, but really had no worthwhile
suggestion to offer.

Perhaps a Form with two side by side subforms to allow you to scroll
separately through two views of the same table would be about as easy as any
approach.

I'm sorry I have no better suggestions.

Larry Linson
Microsoft Access MVP

"Andre Doreid Berro" <DB****@aol.com > wrote in message
news:ac******** *************** ***@posting.goo gle.com...
Greetings All

I have requested help before and I really need your help in this. I
am working on an Access database of contacts. I have plenty of
records but plenty of them are almost duplicates. Data in some fields
are misspelled and entered as completly different records. I wonder
how could I clean the database in a way I have single record of
contact information for each contact.

Example:
Record 1

Name:Johns Smith
Tel: 734-123-4567
Address: 123 Any Street
Title: VP of Operations
Company: ACME
Record2
Name: John Smith
Tel: 734-123-4567
Address: 123 Any Street
Title: VP of Operations
Company: ACME

So, record 1 and 2 are basically identical; the name is misspelled.
If someone entered new records because names were misspelled, how can
I go and clean the data of such records.

Best Regards
A.D.B.

Nov 12 '05 #2

"Andre Doreid Berro" <DB****@aol.com > wrote in message
news:ac******** *************** ***@posting.goo gle.com...
Greetings All

I have requested help before and I really need your help in this. I
am working on an Access database of contacts. I have plenty of
records but plenty of them are almost duplicates. Data in some fields
are misspelled and entered as completly different records. I wonder
how could I clean the database in a way I have single record of
contact information for each contact.

Example:
Record 1

Name:Johns Smith
Tel: 734-123-4567
Address: 123 Any Street
Title: VP of Operations
Company: ACME
Record2
Name: John Smith
Tel: 734-123-4567
Address: 123 Any Street
Title: VP of Operations
Company: ACME

So, record 1 and 2 are basically identical; the name is misspelled.
If someone entered new records because names were misspelled, how can
I go and clean the data of such records.

Best Regards
A.D.B.

Best idea is to visit the MVP site
http://mvp.org
Bob "Buckwheat" Simms, MVP
Nov 12 '05 #3
Depending on what data is 'important' to you it may not be that
difficult. It seems that you can match on a few fields such as zip
code, phone # and last name....once you do that you can assoc. all
similiar records togther and move the unique to a NEW table (this way
you can compare the results without changing any of the original data
before you're ready). I'm assuming that the selection of the first
name or some other non-identifying field is not as important...you can
use max or min do grab one of those fields when combining...... if you
need more help or assistance with the query, etc. let me know.

Me****@Computer SOSNJ.com

"Buckwheat7 2" <ya*******@hotm ail.com> wrote in message news:<a3******* *************** ********@news.t eranews.com>...
"Andre Doreid Berro" <DB****@aol.com > wrote in message
news:ac******** *************** ***@posting.goo gle.com...
Greetings All

I have requested help before and I really need your help in this. I
am working on an Access database of contacts. I have plenty of
records but plenty of them are almost duplicates. Data in some fields
are misspelled and entered as completly different records. I wonder
how could I clean the database in a way I have single record of
contact information for each contact.

Example:
Record 1

Name:Johns Smith
Tel: 734-123-4567
Address: 123 Any Street
Title: VP of Operations
Company: ACME
Record2
Name: John Smith
Tel: 734-123-4567
Address: 123 Any Street
Title: VP of Operations
Company: ACME

So, record 1 and 2 are basically identical; the name is misspelled.
If someone entered new records because names were misspelled, how can
I go and clean the data of such records.

Best Regards
A.D.B.

Best idea is to visit the MVP site
http://mvp.org
Bob "Buckwheat" Simms, MVP

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.