mis spelling of name allows duplicate by mistake

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The problem is with names of roads. The field for road name is set to no
duplicates. A user entered a new road by typing Allens Road, when there is
already Alans Road, this second is correct spelling. The database allowed
this because it looks like two different roads, but is really the same road
entered twice. How would I code the Dcount function to search for similarly
spelled roads to warn the user and prevent duplication? Thanks for any help
with this, I haven't been able to find any answers by searching.
 
Thanks Doug, I will certainly investigate this Soundex thing, thanks for the
web site. I imagine that it is a very common situation to want to prevent
duplicates that occur with mis spelling of names. I have read Albert Kallal's
ideas on search then add new if not found, will investigate this as well.

What do developers normally do with this type of situation, I wonder. Maybe
companies just put up with having apparent customers who are really the same
person with two different spellings.

:


I just did a Google search on Soundex, and the following may be of use
to you:
http://www.experts-exchange.com/Databases/MS_Access/Q_21243472.html

-- Doug Steele, Microsoft Access MVP
 
What do developers normally do with this type of situation, I wonder. Maybe
companies just put up with having apparent customers who are really the same
person with two different spellings.
Click to expand...

With street names, which is where this thread started, you can get a
long way by integrating your national postal address database (if there
is one) into the application. In the United Kingdom, which has
fine-grained postcodes (typically at least one postcode per street and
one per large building) this is very common. Data entry begins with the
postcode; the rest of the address is retrieved from the database,
leaving (90% or more of the time) only the house number to fill in.

Even in places with coarse-grained postal codes (e.g. Australia), a
postal address database lets you check the street name against the
locality and postcode and flag most errors. In the US, Zip is coarse but
Zip+4 is fine.

Soundex is a useful approach for finding approximate matches, but it may
throw up too many false positives to be useful. If you want to get deep
into this, do a web search on "Levenshtein Distance": the distance
between two strings is the number of changes that need to be made to
convert one to the other.
 
Liz said:
What do developers normally do with this type of situation, I wonder. Maybe
companies just put up with having apparent customers who are really the same
person with two different spellings.
Click to expand...


Actually, I have found that to be true more often than not.
In many situations it doesn't really matter, but when it
does, it can be a nightmare. As Doug said, there's no way
to determine if two similar names are a misspelling or two
different entities.

One thing that I usually do is provide a combo box for them
to select from a list of names that have been entered
previously. Another is to prevent them from entering a new
name altogether and make them ask the data administrator
person to add the name before they can use it (clumsy bit of
bureaucracy, but sometimes quite effective).

If worst comes to worst, I will provide them with a data
management form that tries to search/clump similar names
together and allow them to consolidate selected names to the
correct one. I had one case where the client was recording
the customer's country for plotting a graph of sales volume.
The graph was a complete mess with many different entries
for the same country. E.g.
England
English
United Kingdom
UK
U.K.
U. K.
Great Briton
GB
G.B.
G. B.

I know those are not all the same political/geographic
entities (ask the Welsh or Scotts), but try to explain that
to sales people ;-)
 
Thank you very much for your replies. Why do I suddenly get the feeling that
databases aren't as wonderful as I had thought.
 
Thank you very much for your replies. Why do I suddenly get the feeling that
databases aren't as wonderful as I had thought.
Click to expand...

Well, they're not magical. Think about it: if you had the same problem
without a computer at all, how would you (unless you had all the
street names in town memorized) be sure that 1320 Allen Street and
1320 Alan Street were in fact the same address?

John W. Vinson[MVP]
 
Back
Top