Query comparing non-identical fields

[Guest]

I have two tables with telephone numbers in them. They were imported from
Excel files.
Table one has the number listed as xxx-xxx-xxxx xxx with the dashes as part
of the actual text, then a space and a 3 digit cus code after it.
Table two has phone #'s listed as xxxxxxxxxx this one does not have any
dashes as part of the text and does not contain the cus cocde.

I need to find any matches between the two tables. Eg. All records where
table 1 is 763-555-5555 000 and table two is 7635555555. How do I write this
statement to find the matches?

 

[Brendan Reynolds]

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2 ON Replace(Left$([Table1].[TestText],
InStr(1,[Table1].[TestText]," ")-1),"-","") = [Table2].[TestText]
WHERE Table1.TestText IS NOT NULL;

Where Table1.TestText is the field with the hyphens and customer number, and
Table2.TestText is the one without.

You can't create queries like this (with an expression in the JOIN clause)
using the query design grid, you have to do it in SQL view.

This query may be slow if there is a large number of records.