Join Queries - can you join two tables with similar records, not an exact match?

  • Thread starter Thread starter John Goodrich
  • Start date Start date
J

John Goodrich

I have a main dbase. I have a separate table with Business
Names. I'm looking to match as many records in the main
dbase with the Business names database - but realize there
may not be exact matches.
Is there a way to pull over records that begin with , or
are like the data in the business names database?

I know you can do it one at a time in the query columns,
but I need to do it to many names.

help.
 
I have a main dbase. I have a separate table with Business
Names. I'm looking to match as many records in the main
dbase with the Business names database - but realize there
may not be exact matches.
Is there a way to pull over records that begin with , or
are like the data in the business names database?

I know you can do it one at a time in the query columns,
but I need to do it to many names.
Click to expand...

You can indeed use a "non-equi join"... but matching business names is
going to be VERY difficult. For example, "MLPF&S" and "Merrill Lynch,
Pierce, Fenner and Smith" are two ways of referring to the same
company - but you're not going to convince Access of that! Similarly,
"J. C. Watson & Co." and "John Watson Produce Packing" are two
different companies - John quarreled with his dad a few years back and
set up his own business.

For an arbitrary, partial, and error-prone join that might help at
least whittle down the problem <g>, try creating a Query joining the
two tables on CompanyName. Then open the query in SQL view and edit
the clause

FROM Business1 INNER JOIN Business2
ON Business1.CompanyName = Business2.CompanyName

to

FROM Business1 INNER JOIN Business2
ON Business1.CompanyName LIKE Left(Business2.CompanyName, 8) & "*"

Then reverse the roles of the two names, or play around with the
various Mid() functions to (say) extract the second "word" of each
name.

John W. Vinson[MVP]
(no longer chatting for now)
 
Back
Top