Zip Code Query

[Mike]

I am working with two tables. Each table contains a field for a zip code.
Table A has an 11 digit zip code and Table B has an 11 digit zip code. I
currently run a join query to capture all matching records.

Table B also has zip codes less than 11 digits. I am trying to write a query
which will take each zip code less than 11 digits and match on the length of
the string to Table B. The string length can be between 5 and 9 digits. If
there a way to do this?

Thanks,
Mike

 

[Michel Walsh]

You can use LIKE in the ON clause of the join:



SELECT tableA.zip, tableB.zip
FROM tableA INNER JOIN tableB
ON tableA.zip LIKE tableB.zip & "*"


Note that any match will be listed, so, if tableB as 3 (part of) zip codes
matching tableA.zip, all the three matches will be supplied, NOT JUST THE
MOST restrictive one.



Vanderghast, Access MVP

 

[John Spencer]

It sounds as if you might want to use a non-equi join in your query.

Open your query in SQL view

Find text that reads something like

TableA INNER JOIN TableB ON TableA.ZipCode = TableB.ZipCode

Edit the text to
TableA INNER JOIN TableB ON TableA.ZipCode LIKE TableB.ZipCode & "*"

You cannot work with the modified query in Design View (query grid).

Also you may need to add to the Where clause to exclude some matches.

AND TableB.ZipCode is Not Null

Another method would be to change the join to
TableA INNER JOIN TableB ON Left(TableA.ZipCode,5) = Left(TableB.ZipCode,5)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..