Join Query

[Guest]

I have two tables (bds and ta) where each table includes a zip code that can
range from a minium of 5 digits to a maximum of 11 digits. I am trying to see
if it is possible to write a query to match on the zip code field for each
table. I am running into a problem when the bds table has 9 digits and the ta
table has 11 digits. If the number of digits are not the same then the record
is excluded. I am trying to pass each zip code from the bds table to the ta
table to see if it matches on any of the string of characters. I hope I
exlplained myself alright.

Thanks

 

[Michel Walsh]

You can join a expression:


SELECT *
FROM a INNER JOIN b ON a.zipCode LIKE b.zipCode & "*"


as example, assuming b.zipCode is shorter than a.zipCode and that means you
have a match if the supplied 'code' is totally matched by a longer one.



Unfortunately, if you have

12345:98760


it will match code 12345* AND code 12345:987*. So, you will have the
two matches, not just the 'fittest' match, not only the 'most complete'
zipCode.


Vanderghast, Access MVP

 

[Guest]

If you tables consistently have code larger in one than the other then try
criteria instead of join. Like [SmallerSizeZip] & "*"

I never heard of Zip codes other than 5 or 9 digits. I have heard of Postal
Codes (other than USA) that have varing size codes.

 

[Guest]

Thanks!

You can join a expression:


SELECT *
FROM a INNER JOIN b ON a.zipCode LIKE b.zipCode & "*"


as example, assuming b.zipCode is shorter than a.zipCode and that means you
have a match if the supplied 'code' is totally matched by a longer one.



Unfortunately, if you have

12345:98760


it will match code 12345* AND code 12345:987*. So, you will have the
two matches, not just the 'fittest' match, not only the 'most complete'
zipCode.


Vanderghast, Access MVP