hello all,

i'm looking for a way to either join or process non-exact matches from an
access 97 db.

for instance, we have a table of names we are not supposed to call (table 1)
.  i am currently joining this table to another table (table 2) to see if
there are any names in table 2 that we are not supposed to call.

obviously access will only find a match if it is exact.  but if the name is
misspelled in table 2, we will never catch it.

i know sql server has the DIFFERENCE and SOUNDEX functions that we could
use, if my client would switch to sql server... but they won't (at least not
yet).

so the question is, does anyone know of a way to catch these non-exact
matches?

thanks in advance,

trevor steinberg

The Soundex function is in the public domain. Once you find it, you
can run a query like:
select Soundex(LastName) from Customers

-Tom.

You can make a fuzzy logic function. Make a two queries, one for each
table. In each query make a column FL: Fuzzy([Name]). Do a third query
and join the FL columns. Considering the probabilities, this won't work
too well though.

I suggest assigning an autonumber field in each table and use that for a
unique ID in each table. Make a third table with no primary key to
reference the other two tables, have two ID columns. Each column would
be many to one on the other two tables.

Perform a one time process with takes each entry of the main table and
looks up all possibilities which might occur in the bad name table. Look
at several real examples of how the bad names occur compared to the good
names. There will be several possibilities, and it will take the process
a long time to run. It will however create the reference table.

Use the same algorithms you come up with and place them in the real time
module so each time a new name is added it will perform the analysis.

Save the original process, so when you modify the algorithms over time
you can wipe out the reference table and recreate it.

--

http://www.members.tripod.com/dmcdivitt
http://www.geocities.com/dmcdivitt

Joe Foster has Soundex code at his site
<http://members.ricochet.net/~jfoster/>

--

Beer, Wine and Database Programming.  What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://webhome.idirect.com/~djsteele/

Hi tjs,

There may be a better way to do what you are up to.

I use Soundex in a large database and it has some shortcomings that need to be
considered. While it will find similar spellings, names can be screwed up in a
fashion that is not similar spelling. For instance, some first and last names
are reversed. Soundex fails on reversed names. The data entry clerk may not
spell the name correctly by leaving out a key character used in the Soundex
algorithym. Soundex may fail on typos.

Since you say you are trying to join a list of names not to call, this implies
that there are phone numbers shown in some field in each table. Why not join on
the phone number fields in the respective tables?

You can do a join on any field, it does not have to be a primary key field.

Another alternative would be to clean up your data.

Here are a couple of tips for finding bad data: (1)If you would do other joins
on important fields such as addresses, phone numbers, or other data that is
supposed to be the same for each table, it may be instructive to you in finding
bad data. (2)Do an A-Z sort and a Z-A sort on each field. Most bad data will
usually be at either the bottom or the top of any list when the list is sorted.
(3)Do a Find Duplicates query on important fields. You may find duplicate phone
numbers with incorrect names, for instance.

Hope this helps,

Norm

hi norm,

regarding your thoughtful reply...

because that's not good enough for my client... the data we/ they get to
work with is, quite frankly, crap.

you'd think that given the nature of the project this is for (satisfaction
surveys for high-end support clients of a nameless major software company),
that they'd actually be able to have a primary key for all the people they
want to call, but no.

as a result, we compare on the combination of company name, first name and
last name, on the phone number, and on the project code (whatever that is).
and still some slip through the cracks due to crappy data from my client's
client.

yes, that would be a most excellent idea, but it is not an option for these
people (or at least that's what they say).  it's amazing that this software
company has not collapsed into ruin with this kind of organization.

thanks for your input though.  everything you say is totally true, but i'm
sure you are painfully aware of the kind of nonsense i'm up against here.
so i wrote a soundex function (after finding the algorithm) to use.

thanks again,

trevor

People see what they want to see, as well as making typos

Variations on my name are
Stoodart-Jones
Stoddart-Stone
Stewart-Jones
Mr S Tones

 thank God the postman has fuzzy logic!

Have Fun
Robin

The postman may but the post office in my small town certainly doesn't have any fuzzy
logic.

Background: I live in a small town of 4000 popn.  Downtown is two blocks long.  The
post office is at one end of downtown.

A friend moves his store down four doors into a bigger space.  He tells post office
of his new address.  But one clerk in the PO returns all the mail she sees going to
the new address stating that the business isn't at that address.  She didn't realize
it had moved.  Another clerk returns all the mail going to the old address as she
knows the business has moved.  

Now ifn they had just given the mail to the mailperson then that would've been
delivered just fine.  Just down four doors.  But noooo, the clerks in the office
screwed up everything.

Tony

----
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups.
Microsoft Access Links, Hints, Tips & Accounting Systems at
   http://www.granite.ab.ca/accsmstr.htm
VolStar http://www.volstar.com Manage hundreds or
   thousands of volunteers for special events.

long.  The post office is at one end of downtown.
OK. The smallest town I came across in my country consisted of a Pub & a
Trucking Firm. That's it. Always wondered how big "Vermillion" was.

What does this have to do with Access? They never heard of it, that's what!

Any replies need to beat that! Downwards <chortle>