Killer Query of max and min ranges

[aldo jr]

Sample Data Set below.

Table 1:

#, Street Name, Low, High, Edge, Route, Zip,
1, El Camino Real/Hwy 82, 1, 17, Odd, 17C, 94010,
2, El Camino Real/Hwy 82, 1, 1789, Both, 15A, 94030,
3, El Camino Real/Hwy 82, 2, 60, Even, 17C, 94401,
4, El Camino Real/Hwy 82, 19, 298, Both, 15D, 94010,
5, El Camino Real/Hwy 82, 19, 1333, Both, 15C, 94010,
6, El Camino Real/Hwy 82, 100, 1799, Both, 13C, 94066,
7, El Camino Real/Hwy 82, 100, 1099, Both, 11B, 94080,
8, El Camino Real/Hwy 82, 500, 1399, Both, 13B, 94066,
9, El Camino Real/Hwy 82, 1000, 7899, Both, 10C, 94014,
10, El Camino Real/Hwy 82, 1100, 1924, Both, 11A, 94080,
11, El Camino Real/Hwy 82, 1335, 1899, Both, 15B, 94010,

Hi hopefully someone can please help. I am trying to build an access
query that returns to me streets (records) that have overlapping
ranges. For instance; I need to see all of el camino street records
where the ranges over lap (one records high is greater than another
records low) and where the zip code is the same, the street name is
the same, and the route is different.

So from the above example, I would need to see records 4 and 5, and
records 6 and 8.

Thank you

 

[roger]

Add the table to a query twice
do not link them!

If your table is called "Untitled" then the 2nd one comes in as "Untitled_1"

Drag all the fields of the 1st table into the QBE grid
the criteria for Street Name is:
=[Untitled_1].[Street Name]
(same for zip)

route is
<>[Untitled_1].[Route]


the criteria for High is:

=[Untitled_1].[Low] And <= [Untitled_1].[High]

but you may have to work on your logic cuz:
"one records high is greater than another records low) and where the zip
code is the same, the street name is the same, and the route is different."

I think would return every record on that list. at least 3 and 7 anyway

hth

Its just a complex version of "finding duplicate records" which you'll find
in help