Flexible defining of join-properties

[Medioman]

Hi everybody,

I've a problem with the so-called join properties.
Within the design field, I have defined a relationship between a query and
a table:

Query: all_city_names Table: selected_city_names
Fieldname: Cityname <--> Fieldname: Cityname

So I want to filter out the city names from the query "all_city_names" on
the basis of city names which are definied in the table (i.e.
selected_city_names). In principle Access can easily execute this command.

However, some problems arise with records which contains some spelling
mistakes / differences.

First example:

The query contains a record with the city name "Vancouve", while the
definied table only contains the city name "Vancouver".
Result: "Vancouve" isn't filtered out, because of the missing "r"-term.

Second example:

The query contains a record with the city name "NewYork", while the definied
table only contains the city name "New York".
Result: "NewYork" isn't filtered out, because of the missing "space".

Therefore my question is: how to configure a flexibele join-property
relationship which is able to deal with small spelling mistakes /
differences?
Or in other words: is it possible to filter out the asked city records on
the basis of high similarity of characters?


Many thanks!

Medioman

 

[Douglas J Steele]

One possibility is to use Soundex.

I wrote about this (and another similar technique, Levenshtein Distance) in
my April 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Guest]

Hi -- This problem can be solved by your design. For example, rather than
allow users to free type the city names, you should use a combo box
(cascading) or list box so they can choose the name and the key is stored.
This way you can do joins on the key. However, this may not be possible if
there are too many cities for you to manage but is the best way to go.

Another option is to use the Like statement as critieria to attempt to
capture the spelling differences. However, by allowing free typing you'll
never be able to capture them all. For example: NewYork, New York,
NY...include misspelling and it gets worse!