Compare Query

[Novabulldawg]

I have on table that I'm trying to compare two addresses. I would use the dup
query. However, the addresses are set up different (i.e 220 Anywhere Road or
220 Anywhere RD.). How can I bulid a query that will only compare the first
three charaters of the selected fields?

 

[Smartin]

I have on table that I'm trying to compare two addresses. I would use the dup
query. However, the addresses are set up different (i.e 220 Anywhere Road or
220 Anywhere RD.). How can I bulid a query that will only compare the first
three charaters of the selected fields?

This will show you the fields from two tables where the first three
characters in "Addr1" match:

SELECT *
FROM Table1 AS A INNER JOIN Table1 AS B
ON LEFT(A.Addr1,3) = LEFT(B.Addr1,3)

You can create most of this in the query builder by adding your table
twice and drawing a join between the fields in question. You may want to
join than one field (and probably should).

Then switch to SQL view and wrap LEFT( ... , 3) around the field names.

Of course, the effectiveness of this depends heavily on the extent to
which you have similar addresses in your table. Manual review of the
query results will almost certainly be necessary to determine where the
real dups are.

Hope this helps!