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
"aldo jr" wrote:
> 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
>
Similar Threads
