Query

  • Thread starter Thread starter maggie
  • Start date Start date
M

maggie

Does anyone know how to write a query that would search
text strings on an incoming file and compare them to a
table that exists on Access. For example, compare name
between list of already existing names and list of new
names. But the comparison has to not be exact, so that
you can search a similar string.
 
Dear Maggie:

I recommend you do this in separate steps. Bring the text strings
into a table first. Then do the matching.

It's not clear whether you need exact matches, or pattern matching, or
what. More details on that, please. What is a "similar string?" Do
you have an algorithm in mind on which to base this?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I don't need an exact match, I can get that to work.
What I need is to find all names that may match.
Example: One name is 'AutoNation' the other name is 'The
Auto Nation'. I need the query to find the match between
the two. I tried using the like function with the name
of the field I want to watch match on but that doesn't
work. Any help would be appreciated.
Thanks.
 
Dear Maggie:

Well, there is the Soundex function, but using it on a mass compare
would take some real time - like hours or days. Soundex will give you
a numeric value telling you how close the algorithm "thinks" the two
strings sound similar. It believe it is used when you call directory
assistance. The operator types in what you said as close as possible,
then gets some results.

If you want to implement rules like allowing white space before a
capitalized letter in the middle of a word, you're on your own. The
point is, finding that AutoNation is like Auto Nation is not a trivial
exercise, and it's probably only one of thousands of similarity
algorithms you might need.

The human mind is flexible and associative. Computers are not. The
expectation that a computer can do something easily just because we
can do it easily ourselves is very misleading. My computer doesn't
know good music from bad (probably I don't either, but I think I do).
My computer doesn't appreciate sculpture or architecture, while I do
without much exertion.

Good luck on your search. From experience, I'm not very hopeful
unless you are dealing with a few hundred rows in each of the two
sets. If you have a thousand rows in each, the computer would have to
perform a million Soundex operations. That will take a LONG time.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top