linked table using SQL Query

[lee]

zip code zone

100-150 1
150-200 2
200-300 3
300-400 4
310 5


Table 2

facility zip code
ca 105
il 240
mn 270


I have two tables and need to join them and get the result below.
Table 1 zip code field either has zip code in range or as a whole no.
It has to link with the zip code field in table 2

Following result is required using SQL query . Please help

facility zip code zone
ca 105 1
il 240 3
mn 270 3

 

[John W. Vinson]

zip code zone

100-150 1
150-200 2
200-300 3
300-400 4
310 5


Table 2

facility zip code
ca 105
il 240
mn 270


I have two tables and need to join them and get the result below.
Table 1 zip code field either has zip code in range or as a whole no.
It has to link with the zip code field in table 2

Following result is required using SQL query . Please help

facility zip code zone
ca 105 1
il 240 3
mn 270 3

Well... your table 1 does not contain a range. It contains a text string that
a human might choose to interpret as a range (or not; my zip code is
83660-6365 and that's NOT a range even though it has a hyphen). You'll need
some expression to tease apart the two portions of the range. This would be a
heck of a lot easier if your zone 5 weren't embedded in zone 4!

I don't think it will be possible to get an updateable join but you can use a
Cartesian join with a Where clause: untested air code -

SELECT A.Facility, A.[Zip Code], B.Zone
FROM Table2 AS A, Table1 AS B
WHERE A.[Zip code] = B.[Zip code]
OR (B.[Zip code] LIKE "*-*"
AND A.[Zip code] >= Left([B.[Zip code], 3)
AND A.[Zip code] <= Right(B.[Zip code], 3))

This assumes (perhaps overoptimistically) that the data is *all* exactly as
you present it. It also assumes that either your Table 2 is fairly small or
that you're willing to be patient as this inefficient and slow query grinds
through the records...

 

[lee]

zip code  zone

100-150    1
150-200    2
200-300    3
300-400    4
310           5

Table 2

facility   zip code
ca           105
il             240
mn          270

I have two tables and need to join them and get the result below.
Table 1 zip code field  either has zip code in range or as a whole no.
It has to link with the zip code field in table 2

Following result is required using SQL query . Please help

facility   zip code  zone
ca         105       1
il            240      3
mn         270      3

Well... your table 1 does not contain a range. It contains a text string that
a human might choose to interpret as a range (or not; my zip code is
83660-6365 and that's NOT a range even though it has a hyphen). You'll need
some expression to tease apart the two portions of the range. This would be a
heck of a lot easier if your zone 5 weren't embedded in zone 4!

I don't think it will be possible to get an updateable join but you can use a
Cartesian join with a Where clause: untested air code -

SELECT A.Facility, A.[Zip Code], B.Zone
FROM Table2 AS A, Table1 AS B
WHERE A.[Zip code] = B.[Zip code]
OR (B.[Zip code] LIKE "*-*"
       AND A.[Zip code] >= Left([B.[Zip code], 3)
       AND A.[Zip code] <= Right(B.[Zip code], 3))

This assumes (perhaps overoptimistically) that the data is *all* exactly as
you present it. It also assumes that either your Table 2 is fairly small or
that you're willing to be patient as this inefficient and slow query grinds
through the records...

thanks Zip code field in table 1 is a range actually- table 2 zip code
field should be checked if the zip code in table 2 is within zip code
range on table 1. please advise

 

[lee]

zip code  zone

100-150    1
150-200    2
200-300    3
300-400    4
310           5

Table 2

facility   zip code
ca           105
il             240
mn          270

I have two tables and need to join them and get the result below.
Table 1 zip code field  either has zip code in range or as a whole no.
It has to link with the zip code field in table 2

Following result is required using SQL query . Please help

facility   zip code  zone
ca         105       1
il            240      3
mn         270      3

Well... your table 1 does not contain a range. It contains a text string that
a human might choose to interpret as a range (or not; my zip code is
83660-6365 and that's NOT a range even though it has a hyphen). You'll need
some expression to tease apart the two portions of the range. This would be a
heck of a lot easier if your zone 5 weren't embedded in zone 4!

I don't think it will be possible to get an updateable join but you can use a
Cartesian join with a Where clause: untested air code -

SELECT A.Facility, A.[Zip Code], B.Zone
FROM Table2 AS A, Table1 AS B
WHERE A.[Zip code] = B.[Zip code]
OR (B.[Zip code] LIKE "*-*"
       AND A.[Zip code] >= Left([B.[Zip code], 3)
       AND A.[Zip code] <= Right(B.[Zip code], 3))

This assumes (perhaps overoptimistically) that the data is *all* exactly as
you present it. It also assumes that either your Table 2 is fairly small or
that you're willing to be patient as this inefficient and slow query grinds
through the records...

Also i have 1000's of records so i request an efficient query

 

[John W. Vinson]

thanks Zip code field in table 1 is a range actually- table 2 zip code
field should be checked if the zip code in table 2 is within zip code
range on table 1. please advise.

To YOU - as a human being familiar with the data - the field is a range.

To Access - a computer program with zero human intelligence or insight - the
field is a three or seven character text string. There is no such datatype in
Access as "a range".

The query I posted will tease the text string apart into a range. Did you try
it?

Is it at all possible to recast the structure of table1 so that the structure
actually has two fields rather than a text string? If Table1 were set up like:

ZipLow ZipHigh Zone
100 150 1
150 200 2
200 300 3
300 309 4
311 400 4
310 310 5


then a much more efficient and simpler non-Equi-Join query would work:

SELECT A.<whatever>, B.Zone
FROM Table2 AS A INNER JOIN Table1 AS B
ON A.[Zip Code] >= B.ZipLow
AND A.[Zip Code] <= B.ZipHigh;

 

[John W. Vinson]

Also i have 1000's of records so i request an efficient query

Then you must restructure your Table1. It *CANNOT* be made efficient.

 

[lee]

Then you must restructure your Table1. It *CANNOT* be made efficient.

Oh OK Sir, Thank Your. I appreciate your help and insights. I think I
can break that into high zip and low zip even in excel. so i will test
the query and let you know. Thanks

 

[lee]

Oh OK Sir, Thank Your. I appreciate your help and insights. I think I
can break that into high zip and low zip even in excel. so i will test
the query and let you know. Thanks

John

The query is giving multiple duplicate rows when ever zones are same.
can i get your help please offline- it is urgent- can i i get your
email address- mine is (e-mail address removed). i am willing to pay for
your time. Thanks please let me know. I will appreciate your
assistance . Regards

 

[John W. Vinson]

John

The query is giving multiple duplicate rows when ever zones are same.
can i get your help please offline- it is urgent- can i i get your
email address- mine is (e-mail address removed). i am willing to pay for
your time. Thanks please let me know. I will appreciate your
assistance . Regards

Please post the complete SQL of your query, and (if it's not confidential) a
few rows of sample data. I'm not taking new customers at this point, but this
should be pretty straightforward.