Find similar records across two tables

[TA Bran via AccessMonster.com]

Hello all,

I’m looking for a way to find and list all similar records across two tables.
(Both tables contain addresses that must remain separate and there is no
common field to link between them.) After some research, I’ve discovered
this is much more difficult than I originally thought. I simply haven’t been
able to determine how I can accomplish this with the use of wildcards so that
if a record in TABLE1 field ADDR1 is ‘123 Main St’, for example, and a record
in TABLE2 is ‘123 Main Street’ it is shown as a match. The following query
works for exact matches but not for like matches:

SELECT *
FROM Table1
WHERE ADDR1 IN (
SELECT DISTINCT ADDR1
FROM Table2
)

Does anyone have any suggestions? Am I missing something obvious here?

Many thanks in advance,

TA

 

[John Spencer (MVP)]

No you are not missing anything obvious. Weeelll. maybe. Humans are smarter
than computers.

There is no easy way to do this - Google has done a pretty good job, but even
they will ask you "Did you mean ????". That organization has lots of very
clever programmers and sophisticated algorithms to do this type of thing.

 

[TA Bran via AccessMonster.com]

I'm sorry--I should have been more clear: Table2 is a database filled with
addresses that are to be purged from Table1. Its records are abbriviated so
that 123 Main Street has been entered as '123 Main St' and 123 Main Avenue
has been entered as '123 Main Ave'. The idea was I would then be able to do
searches on Table 2 using a wildcard to find matching records in Table1.
(Something such as: like [Addr1] & "*" ) Hope this makes sense...

-TA

 

[John Spencer (MVP)]

An inner join based on a non-equi joining clause might work for you.

SELECT T1.*
FROM Table1 AS T1 INNER JOIN Table2 As T2
ON T1.Addr1 LIKE T2.Addr1 & "*"

If you wanted to set this up as a delete query, you could try

DELETE DistinctRow T3.*
FROM Table1 as T3
WHERE T3.PrimaryKeyField IN
(SELECT T1.PrimaryKeyField
FROM Table1 AS T1 INNER JOIN Table2 As T2

 

[TA Bran via AccessMonster.com]

Thank you John--Your suggestion is the solution I was looking for.

Much appreciated,

TA