How to prevent similar data entry?

[ariansman]

Different users are putting data for our customers. How can we figure if we are making a duplicate record for the same costumers and prevent that? I mean for example a costumer name is “Scarlett Johansson”, and a record is made for her. Then another user is entering the same data for the costumer, but the user types” Scarlett Johanson”. A typo is here. Can access warn that a similar costumer name already exists and let us decide whether to proceed with data entry or not?

 

[Ranman256]

you cant. this is the bad thing about people names, duplicate entries. It is the bane of all databases.
tho,
you could have text boxs (unbound), enter names first,last.
a query can then show the result of this name , but not the various misspellings.

 

[MarkK]

This is called "approximate string matching" and is something that you need to implement if you want it. A couple of algorithms that come to mind are Soundex, and Levenshtein Distance. These are public algorithms that you can implement in your VBA code fairly easily.

 

[jdraw]

I agree with Markk that some algorithms may be useful. Also, I would check other attributes before saving a record. Things like, city, zip code, address, phone number....
I would suggest checking for duplicates/replicates before saving a new record.

If you have a number of repeat Customers, you might want to have a list or combo box and an OnChange event to pull up similarly spelled Names during input.

Good luck. You're not the first person to have this issue.


cross posted @ http://www.accessforums.net/showthread.php?t=62951

 

[Galaxiom]

See Post 10 in this thread for a VBA Weighted Damareau-Levenstein Distance function.

 

[CJ_London]

cross posted here

http://www.accessforums.net/showthread.php?t=62951

OP has been advised of the implications