Sub query help needed

[Bret]

Hi all,
I have two tables...
One with a complete list of addresses and road names (32,000)
The other with a list of addresses and road names as follows (1,500)
Either a pair of records with the same road name, but one is marked
as the high address and the other is marked as low, with the
corresponding address.
Or a single record with the road name, and the only address for that
road
marked as the only address

The first table has many more road names, and addresses than the other
table.
What I am trying to do is...
Select all the records from the first table that match by road name AND
who's address falls in the range of the second tables high and lows,
inclusive.
or who's address equals the only address for that road.

[TABLE 1]
ADDRESS | ROAD_NAME | OTHER FIELDS FOLLOW
27000 WALNUT WAY
27001 WALNUT WAY
27002 WALNUT WAY
..
OTHER ROAD
..
11980 MAPLE STREET
..
OTHER ROADS
..
12001 MAPLE STREET
12200 MAPLE STREET
..
OTHER MAPLE ADDRESSES
..
13100 MAPLE STREET
ETC



[TABLE 2]
ADDRESS | ROAD_NAME | HI_LOW
12000 MAPLE STREET L
13098 MAPLE STREET H
27002 WALNUT WAY O
18002 FIR DRIVE L
18700 FIR DRIVE H
21980 WHITE STREET H
20782 WHITE STREET L

I would want to return
27002 WALNUT WAY
12001 MAPLE STREET
12200 MAPLE STREET
UP TO
13098 MAPLE STREET

so on and so on



Anyone? I need to run this tomorrow morning (thur 04/05/2007)
I spent way too much time cleaning up bad road names in TABLE 1
Not very much experience with joins or subqueries.
I can return all the road name matches, but I am having trouble restricting
the results to within the ranges.

Thanks in advance for any tips and examples

Bret

 

[Tom Ellison]

Dear Bret:

It sounds to me that you could use this:

SELECT T1.ADDRESS, T1.ROAD_NAME
FROM [TABLE 1] T1
WHERE T1.ROAD_NAME IN (SELECT ROAD_NAME
FROM T2) AND
T1.ADDRESS BETWEEN
(SELECT MIN(ADDRESS) FROM [TABLE 2] T2
WHERE T2.ROAD_NAME = T1.ROAD_NAME) AND
(SELECT MAX(ADDRESS) FROM [TABLE 2] T2
WHERE T2.ROAD_NAME = T1.ROAD_NAME)

This assumes that your ADDRESS columns are numeric. If they are text, you
may need to put CInt() function on them inside the query.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison
Microsoft Access MVP

 

[Praxis]

Dear Bret:

It sounds to me that you could use this:

SELECT T1.ADDRESS, T1.ROAD_NAME
FROM [TABLE 1] T1
WHERE T1.ROAD_NAME IN (SELECT ROAD_NAME
FROM T2) AND
T1.ADDRESS BETWEEN
(SELECT MIN(ADDRESS) FROM [TABLE 2] T2
WHERE T2.ROAD_NAME = T1.ROAD_NAME) AND
(SELECT MAX(ADDRESS) FROM [TABLE 2] T2
WHERE T2.ROAD_NAME = T1.ROAD_NAME)

This assumes that your ADDRESS columns are numeric. If they are text, you
may need to put CInt() function on them inside thequery.

Hi Tom,
Thanks for the response. I am going to take a look at it now.
The addresses in TABLE 2 are a string, had to use CLng to cast them.
I also had to use the Nz function with that because of the 'invalid
use of NULL' error
I could not find out where the NULL was

Now don't laugh too hard, but this is what I ended up doing this
morning before your response....

Get the records from the TUOLUMNE.REGISTERED.VOTERS table that fall in
between the High and Low records from the LightingDistricts table

1***LightingDistricts_LOW_ADDR query

SELECT LightingDistricts_AddressedParcels.ID,
LightingDistricts_AddressedParcels.SITUS_ADDR,
LightingDistricts_AddressedParcels.SITUS_STRE,
LightingDistricts_AddressedParcels.SITUS_CITY,
LightingDistricts_AddressedParcels.PARCEL_ID,
LightingDistricts_AddressedParcels.ADDR_RAN,
LightingDistricts_AddressedParcels.FULL_STR_NAME
FROM LightingDistricts_AddressedParcels
WHERE (((LightingDistricts_AddressedParcels.ADDR_RAN)="L"));

1***LightingDistricts_HI_ADDR query***

SELECT LightingDistricts_AddressedParcels.ID,
LightingDistricts_AddressedParcels.SITUS_ADDR,
LightingDistricts_AddressedParcels.SITUS_STRE,
LightingDistricts_AddressedParcels.SITUS_CITY,
LightingDistricts_AddressedParcels.PARCEL_ID,
LightingDistricts_AddressedParcels.ADDR_RAN,
LightingDistricts_AddressedParcels.FULL_STR_NAME
FROM LightingDistricts_AddressedParcels
WHERE (((LightingDistricts_AddressedParcels.ADDR_RAN)="H"));

2***LightingDistricts_LOW_HI_ADDR query

SELECT LightingDistricts_LOW_ADDR.ID,
LightingDistricts_LOW_ADDR.SITUS_ADDR,
LightingDistricts_LOW_ADDR.SITUS_STRE,
LightingDistricts_LOW_ADDR.SITUS_CITY,
LightingDistricts_LOW_ADDR.PARCEL_ID,
LightingDistricts_LOW_ADDR.SITUS_ADDR,
LightingDistricts_LOW_ADDR.FULL_STR_NAME,
LightingDistricts_HI_ADDR.SITUS_ADDR
FROM LightingDistricts_LOW_ADDR LEFT JOIN LightingDistricts_HI_ADDR ON
LightingDistricts_LOW_ADDR.FULL_STR_NAME =
LightingDistricts_HI_ADDR.FULL_STR_NAME
WHERE (((LightingDistricts_HI_ADDR.FULL_STR_NAME) Is Not Null));

3***voting_addr_ranges_final query***

SELECT [TUOLUMNE REGISTERED VOTERS].name_last, [TUOLUMNE REGISTERED
VOTERS].name_first, [TUOLUMNE REGISTERED VOTERS].mail_street,
[TUOLUMNE REGISTERED VOTERS].mail_city, [TUOLUMNE REGISTERED
VOTERS].mail_state, [TUOLUMNE REGISTERED VOTERS].mail_zip, [TUOLUMNE
REGISTERED VOTERS].mail_country, [TUOLUMNE REGISTERED
VOTERS].GIS_Address, [TUOLUMNE REGISTERED VOTERS].house_number,
LightingDistricts_LOW_HI_ADDR.LightingDistricts_LOW_ADDR.SITUS_ADDR,
LightingDistricts_LOW_HI_ADDR.LightingDistricts_HI_ADDR.SITUS_ADDR
FROM [TUOLUMNE REGISTERED VOTERS] LEFT JOIN
LightingDistricts_LOW_HI_ADDR ON [TUOLUMNE REGISTERED
VOTERS].GIS_Address = LightingDistricts_LOW_HI_ADDR.FULL_STR_NAME
WHERE (((LightingDistricts_LOW_HI_ADDR.FULL_STR_NAME) Is Not Null));

4***VotingResultsHiLow Query***

SELECT voting_addr_ranges_final.name_last,
voting_addr_ranges_final.name_first,
voting_addr_ranges_final.mail_street,
voting_addr_ranges_final.mail_city,
voting_addr_ranges_final.mail_state,
voting_addr_ranges_final.mail_zip,
voting_addr_ranges_final.mail_country,
voting_addr_ranges_final.GIS_Address,
voting_addr_ranges_final.house_number,
voting_addr_ranges_final.LightingDistricts_LOW_ADDR.SITUS_ADDR AS
Expr1, voting_addr_ranges_final.LightingDistricts_HI_ADDR.SITUS_ADDR
AS Expr2
FROM voting_addr_ranges_final
WHERE
(((voting_addr_ranges_final.house_number)<=CLng(Nz([LightingDistricts_LOW_HI_ADDR]!
[LightingDistricts_HI_ADDR.SITUS_ADDR],0))));

Get the matches for records marked as only address in
LightingDistricts table from the TUOLUMNE.REGISTERED.VOTERS table

1***LightingDistricts_Only_ADDR query

SELECT LightingDistricts_AddressedParcels.ID,
LightingDistricts_AddressedParcels.SITUS_ADDR,
LightingDistricts_AddressedParcels.SITUS_STRE,
LightingDistricts_AddressedParcels.SITUS_CITY,
LightingDistricts_AddressedParcels.PARCEL_ID,
LightingDistricts_AddressedParcels.ADDR_RAN,
LightingDistricts_AddressedParcels.FULL_STR_NAME
FROM LightingDistricts_AddressedParcels
WHERE (((LightingDistricts_AddressedParcels.ADDR_RAN)="O"));


2***VotingResultsOnly query ***

SELECT [TUOLUMNE REGISTERED VOTERS].name_last, [TUOLUMNE REGISTERED
VOTERS].name_first, [TUOLUMNE REGISTERED VOTERS].mail_street,
[TUOLUMNE REGISTERED VOTERS].mail_city, [TUOLUMNE REGISTERED
VOTERS].mail_state, [TUOLUMNE REGISTERED VOTERS].mail_zip, [TUOLUMNE
REGISTERED VOTERS].mail_country, [TUOLUMNE REGISTERED
VOTERS].house_number, [TUOLUMNE REGISTERED VOTERS].GIS_Address,
LightingDistricts_Only_ADDR.SITUS_ADDR
FROM [TUOLUMNE REGISTERED VOTERS] LEFT JOIN
LightingDistricts_Only_ADDR ON [TUOLUMNE REGISTERED
VOTERS].GIS_Address = LightingDistricts_Only_ADDR.FULL_STR_NAME
WHERE ((([TUOLUMNE REGISTERED
VOTERS].house_number)=CLng(Nz([LightingDistricts_Only_ADDR]!
[SITUS_ADDR],0))) AND ((LightingDistricts_Only_ADDR.FULL_STR_NAME) Is
Not Null));