Min and Max query

[aldo jr]

Help Needed. I need to build an access query that compares numerical
ranges in minimums and maximums. The two tables that are needed to
build the query are the following.

table 1: "Load Chart" has approximately 5,500 records

with fields:

1) Street (text)
2) Low (Number)
3) High (Number)
4) Edge (text)
5) Sequence (Number)

table 2: "Pull Chart" has approximately 4,400 records

with fields:

1) Street (text)
2) Low (number)
3) High (number)
4) Edge (text)
5) Route (text)
6) Loop (number)
7) Zip (number)

The query i am attempting to build must return all records in table 1
"load chart" table and bring it's associated zip code from table 2
"pull chart" table while honoring the zip code range.

For example, for street A, has a low of 1 and a high of 184, the query
must search for street a on table 2, and compare the low and high
number values of both tables and return the respective zip code for
that given range.

I'll try to explain agin. if for instance street A on table 1 has a
low of 1 and a high of 184. The query will check the low and high
number values on table 2 and return the appropiate zip code for street
A from table 2.

Sample data set.

Table 1 "Load Chart"

Street Name Low High Edge Sequence
El Camino Real 3 97 Both 90
El Camino Real 2 98 Both 90
El Camino Real 1716 1798 Both 190
El Camino Real 1800 1898 Both 200

Table 2 "Pull Chart"

Street Name Low High Edge Route Loop Zip
El Camino Real 2 98 Both 11B 11 94080
El Camino Real 1716 1898 Both 15B 15 94010

 

[John Spencer]

SELECT L.*,
P.Zip
FROM [Load Chart] as L INNER JOIN [Pull Chart] as P
ON L.Street = P.Street
AND L.Low <= P.High
AND L.High >= P.Low


By the way, storing ZIP as a number is not the wisest idea. You are not
going to do arithmetic on it, so it probably should be stored as a text
field.

How do you store a 9 character zip code? 01234.5680? That would end up
being stored as 1234.568. Of course in your particular case it might be
a good idea if you are going to be working with 9 digit zip codes.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================