This should be easy! Help!

[sccofer]

I can't figure out this problem. Any adivce would be appreciated!

I have two tables that look like this:

tbl_Source tbl_Zips
---------- --------
zipStart zipCode
zipEnd cityName
geoCity countyName
geoCode
cityName

In table source the zipStart and zipEnd is a range say 13217 would be start and 13221 would be end. cityName will not necessarily match between the two tables, spelling errors etc.

I am trying to get all of the correct geocodes for the zips in tbl_Zips. I am having a problem getting all of the results from tbl_Source. I can get all of the results were the zips codes match. ie start and end are the same as the zip I am querying on, but when the zip from tblZip falls in between the zipStart and zipEnd I am having problems. Lets say there are four records in tblSource for zip code 13219. They are:

zipStart zipEnd geocode
-------- ------ -------
13219 13219 geocode1
13219 13219 geocode2
13219 13219 geocode3
13217 13221 geocode4

How do I join these tables to get all of the results. If I join zip to either zip_start or zip_end I only get the 3 results and not the one where the zip falls in the range. How can I structure my query to return all four records? Thanks for any help!

 

[sccofer]

I should add when I query the tblSource by itself with the following I get all 4.

zipStart <=13219
zipEnd >=13219

I run into problems when I add the tblZip to the query and try and get the same result where zip=13219.

 

[Golom]

Try
[tt]
Select S.zipStart, S.zipEnd, Z.GeoCode
From tbl_Source As S, tbl_Zips As Z
WHERE Z.zipCode BETWEEN S.zipStart AND S.zipEnd
[/tt]

 

[sccofer]

Your a lifesaver! Thanks much!!!