zip code by range with two tables 1

[evr72]

Hello,

I have two tables, one that contains all the U.S. zip codes, city and state and a second table that contains 3 fields zip codemin, zip codemax, renumber
I would like to get the rep number to the zip codes table. but not sure how to go about it.
if I try to join the zip code it will only give me that one that matches either the zip codemin or the zip code max
anybody has an idea of how I could join both tables?

here is an example of the data in my tables

min max table
Minimum	Maximum	OEMTerrNo
00001	00999	460
01000	06999	490
07000	07999	500
08000	08699	580
08700	08999	500
09000	09999	500
10000	11999	500

zip code table

zip code	city 	state
00501     	Suffolk                       	New York                      
00544     	Suffolk                       	New York                      
00601     	Adjuntas                      	Puerto Rico                   
00602     	Aguada                        	Puerto Rico                   
00603     	Aguadilla                     	Puerto Rico                   
00604     	Aguadilla                     	Puerto Rico                   
00605     	Aguadilla                     	Puerto Rico                   
00606     	Maricao                       	Puerto Rico                   
00610     	Anasco                        	Puerto Rico                   
00611     	Utuado                        	Puerto Rico                   
00612     	Arecibo                       	Puerto Rico

any thoughts are welcome.

thanks!!

 

[SkipVought]

Hi,

SELECT *
FROM [MIN MAX] MM, [ZIP CODE] ZC
WHERE ZC.[ZIP CODE] >= MM.MINIMUM
  AND ZC.[ZIP CODE] <= MM.MAXIMUM

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[doquigley]

Skip this was a prayer answered, I went on expecting to have to trawl through many threads to find an answer to my question but i found it in the very first thread. My query was very similar in that I had GPS points for Bin lifts in the lifts table L, and I had upper and lower bound GPS points of operational yards in the boundaries table B, and I was looking for those lifts that were lifted in the yard and I was also looking for those lifts that were not lifted within the yard boundaries.

ID	YardName	UpperLat	UpperLon	LowerLat	LowerLon
8	Tullamore	53.298028	-7.521413	53.294899	-7.51405
9	Midlands	53.330861	-7.31562	53.328618	-7.311736
10	NEYard	54.001981	-6.385123	54.001306	-6.382683
11	Cavan	53.990011	-7.329924	53.989002	-7.32516
12	Waterford Hire	52.206558	-7.1161	52.205411	-7.113246
13	Waterford	52.231407	-7.146722	52.230681	-7.145236

SELECT L.*
FROM MyYardBoundaries AS B, MyTodaysLifts AS L
WHERE (((L.Lat)<[B].[UpperLat] And (L.Lat)>[B].[LowerLat]) AND ((L.Long)>[B].[UpperLon] And (L.Long)<[B].[LowerLon]));

This above worked perfectly for lifts within the yard (I used top left as upper bound and bottom right as the lower bound, should have been the opposite corners)
I assumed that to get those lifts outside the yard would be a simple inversion but this does not work, returns no records even if just work on the Lat's, as I tried below

SELECT L.*
FROM MyYardBoundaries AS B, MyTodaysLifts AS L
WHERE (((L.Lat)>[B].[UpperLat] And (L.Lat)<[B].[LowerLat]));

If you could be so good as to point me in the right direction that would be great
Cheers

 

[SkipVought]

How about this...

WHERE (((L.Lat)>B.[UpperLat]
   OR (L.Lat)<B.[LowerLat]))

Think of it this way. Your L.Lat cannot be BOTH above the UpperLat AND below the LowerLat!

I find it very helpful to draw a picture of ALL the possible options.

Furthermore, I think that your in-the-yard solution needs L.Lat<=B.UpperLat etc. not just < or >

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[doquigley]

Thanks Skip, yes I need a kick in the head for mixing my ANDs and ORs up, However what was going on in my head was that I looking for Lats that were above and lats that were below the bounds of the yards. I tried what you suggested with the Lat and Longs but did not get the result that I expected (about half of expected) see below

SELECT L.*, L.Long
FROM MyYardBoundaries AS B, MyTodaysLifts AS L
WHERE (((L.Lat)>[B].[UpperLat] Or (L.Lat)<[B].[LowerLat])) AND (((L.Long)>[B].[UpperLon] Or (L.Long)<[B].[LowerLon]));

Less Brackets

SELECT L.*, L.Long
FROM MyYardBoundaries AS B, MyTodaysLifts AS L
WHERE (((L.Lat)>[B].[UpperLat] Or (L.Lat)<[B].[LowerLat]) AND ((L.Long)>[B].[UpperLon] Or (L.Long)<[B].[LowerLon]));

SELECT L.*, L.Long
FROM MyYardBoundaries AS B, MyTodaysLifts AS L
WHERE (((L.Lat)>[B].[UpperLat]) AND ((L.Long)>[B].[UpperLon])) OR (((L.Lat)<[B].[LowerLat]) AND ((L.Long)<[B].[LowerLon]));

Is there a tidy way of getting an exclusion (Not Exists) result from the 'Within the Yard' query that works that would have to yield the correct result.

(I dropped the = for clarity as the yards are remote enough and the bounds are specified to decimal places beyond the scope of the recording gear)

Thanks again so much

 

[SkipVought]

Does it matter that the Lat is out of bounds and the Long is in bounds? Wouldn't that data point be outside the yard?

So I'd think that if the Lat is out of the yard OR the Long is out of the yard, in either case, rather than both needing to be out of the yard, should qualify as an out of the yard data point.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

So, here's the bottom line.

Your in-the-yard query (tweeked for <= and >=) gets you the correct result.

So NOT (in-the-yard) should give you any data point out of the yard.

SELECT L.*
FROM MyYardBoundaries AS B, MyTodaysLifts AS L
WHERE NOT (
(((L.Lat)<=[B].[UpperLat] And (L.Lat)>=[B].[LowerLat]) AND ((L.Long)>=[B].[UpperLon] And (L.Long)<=[B].[LowerLon]))
);

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[doquigley]

Skip ,yeah it does matter a bit as that would exclude valid lifts to the east and west of the yards, whilst that wouldn't amount to a whole lot of data points within this data set, the result with just using the Lats as suggested does not give near the correct result, only about 60%. I think as there are multiple sites that one 'in yard' point is above another yards outer boundary and vice versa, but then I would expect all data points to be returned not just between 50% and 60%. And I have taken your advice and been drawing it out and still have difficulty getting my head around and translating it to SQL, but again thanks so much for your input.

 

[SkipVought]

I guess I don't understand your requirements.

I wrongfully assumed that any data point not in-the-yard would qualify.

Why would east or west of the yard matter? How is "lift" significant and what does "lift" mean.

Where have I gone off the tracks?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Maybe we need a sample data set for MyTodaysLifts

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[doquigley]

Apologies Skip, I was replying to your 18:27 comment and never saw your 18:39. I have tried your NOT and some other variants like below but they all come up short. The story is that we have refuse trucks lifting/emptying refuse bins from households and there are some anomaly bins that get lifted/emptied in the various depot yards (Bins returned from lost customer accounts) that are to be excluded from the analysis of the household bin lifts, we operate a charge per lift/kg basis so all lifts have to be accounted for. A lift is the term used when a bin is emptied, my bad, you work with these terms every day and never consider an outsider's interpretation or understanding.

SELECT MyTodaysLifts.* FROM MyTodaysLifts
WHERE NOT EXISTS
(SELECT L.*
FROM MyYardBoundaries AS B, MyTodaysLifts AS L
WHERE  (((L.Lat)<[B].[UpperLat] And (L.Lat)>[B].[LowerLat]) AND ((L.Long)>[B].[UpperLon] And (L.Long)<[B].[LowerLon])));

Have attached the MyTodaysLifts, and a sample below the only fields of real interest are the Lat - Long

LiftID Tag TagID Account LiftDate LifterNumber Lat Long
14160414 0108CFC973 154816 12003511 15/05/2017 07:16:40 Lifter2 53.845861667 -6.861673333
14160416 01035DC20B 139288 12008448 15/05/2017 07:08:27 Lifter1 53.847605 -6.882778333
14160417 0103DEEF9D 141376 12008832 15/05/2017 06:38:43 Lifter2 53.816768333 -6.888883333
14160423 0102133F0E 136672 12010711 15/05/2017 08:57:36 Lifter2 53.825095 -6.804076667

I hope this makes things a bit clearer, I will sleep on it and have a fresh look at in the am
Thank you again so much Skip

 

[doquigley]

Attached the yard boundaries file as well so you can see the full picture. There are 18 lifts within the boundaries and there are 11,181 lifts in total in 'MyTodayLifts' which should give 11,163 which is what I am trying to achieve

 

[SkipVought]

I get 11,181 as the count for ...

WHERE NOT 
(
   (((L.Lat)<[B].[UpperLat]
 And (L.Lat)>[B].[LowerLat])
 AND ((L.Long)>[B].[UpperLon]
 And (L.Long)<[B].[LowerLon]))
)

In fact, I IMPORTED your two sheets from the two workbooks you posted into another workbook. Or you could import the MyTodaysLifts sheet into your MyYardBoundaries workbook and then in a third sheet perform a query like this, as I did...

Select COUNT(*) AS [Non-Residential Count]
From 
(
SELECT DISTINCT  L.*
FROM 
  `C:\Users\Skip\Documents\tt-db3.xlsx`.[MyYardBoundaries$] AS B
, `C:\Users\Skip\Documents\tt-db3.xlsx`.[MyTodaysLifts$] AS L
WHERE NOT 
(
      L.Lat <=B.UpperLat 
  And L.Lat >=B.LowerLat
  AND L.Long>=B.UpperLon
  And L.Long<=B.LowerLon
)
)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[doquigley]

I run this query and I still get the full count of 11,181 which in a sense is saying that a point inside one yard is by default outside another yard and therefore all points are counted.
What I have done is made a query of a subquery (points inside the yards) LEFTJOIN/IS NULL which is working with two distinct sets of data and returns the correct amount of data points of 11,163 which is excluding the 18 points in the yards. I will continue to work on trying to make it a single sql statement.

SELECT MyTodaysLifts.*
FROM MyTodaysLifts LEFT JOIN TruckSummary3InsideYardsGIS ON MyTodaysLifts.LiftID = TruckSummary3InsideYardsGIS.LiftID
WHERE (((TruckSummary3InsideYardsGIS.LiftID) Is Null));

Skip thanks again for all your help and patience and I have learned well

 

[SkipVought]

Where was my head? Sorry.

A simpler approch is to subtract the residential count (18) from MyTodaysLifts count (11,182). Why strain out a gnat, when the camel is so obvious, unless you just want the academic exercise.

Glad, however, to add my two cents, with your good sense to get some assents, to follow a scent, to climb the ascent of knowledge.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]