By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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.google.c om...
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

P: n/a

"Andre Doreid Berro" <DB****@aol.com> wrote in message
news:ac**************************@posting.google.c om...
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

P: n/a
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****@ComputerSOSNJ.com

"Buckwheat72" <ya*******@hotmail.com> wrote in message news:<a3******************************@news.terane ws.com>...
"Andre Doreid Berro" <DB****@aol.com> wrote in message
news:ac**************************@posting.google.c om...
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 discussion thread is closed

Replies have been disabled for this discussion.