Trouble working with not between

[Todd W]

I have a database setup with street names and valid address ranges
(tbl_segme). In another table, I have customer addresses (tbl_unpub).
I
want to compare the addresses of the customers to ensure my street
ranges are
correct. Each table has a text_id value that associates each unique
street
name to a number. For example, Cherry Street may have text_id 1904,
Main
Street a text_id of 8894, and so on. tbl_segme has address ranges
broken out
into FRL, TOL, FRR, TOR. Those represent valid ranges From Left - To
Left,

From Right - To Right.

In tbl_unpub, I have the following records:
1 WINDMILL LN
23 WINDMILL LN
154 WINDMILL LN
177 WINDMILL LN

In tbl_segme, I have 4 records for WINDMILL LN with the following
ranges:
FRL value of 28, TOL value of 68, FRR value of 23, TOR value of 69
FRL value of 72, TOL value of 112, FRR value of 87, TOR value of 107
FRL value of 118, TOL value of 118, FRR value of 121, TOR value of 121
FRL value of 131, TOL value of 185, FRR value of 140, TOR value of 186

Text_id values are equal for tbl_unpub and tbl_segme.

According to those entries in tbl_segme, I should only have 1 row in my
tbl_temp for 1 WINDMILL LN. However, when I run the query below, I end
up
will all 4 records from tbl_unpub.

SELECT DISTINCTROW TBL_UNPUB.*, TBL_UNPUB.addr INTO TEMP_TBL3
FROM TBL_UNPUB INNER JOIN tbl_segme ON TBL_UNPUB.text_id =
tbl_segme.TEXT_ID
WHERE (((TBL_UNPUB.addr) Not Between ([tbl_segme].[frl]) And
([tbl_segme].[tol]) And (TBL_UNPUB.addr) Not Between
([tbl_segme].[frr]) And
([tbl_segme].[tor])) AND ((TBL_UNPUB.MUN)=([tbl_segme].[munl])));

Any ideas? If you need further info about the schema, let me know.

Thanks for your time and consideration!
Todd W

[pietlinden]

Seems to me you almost have to break out the *number* of the address
from the street name. Then this should be easy - or at least
significantly easier, because you can do something like

STREETName='MAIN STREET'
AND StreetNumber BETWEEN 10 and 50;

[Todd W]

Thanks for the reply.

The address number is in a separate column, tbl_unpub.addr.

I should have mentioned that tbl_unpub.addr, tbl_segme.frr,
tbl_segme.tor, tbl_segme.frl, and tbl_segme.tol are all integers. So
the numeric comparisons should be possible.

I was careful to exclude street names (text) from this query because of
the headaches involved with finding equal values.

Thanks for taking a look, though. It's appreciated!

Todd W

[Gord]

Try using SELECT DISTINCT instead of SELECT DISTINCTROW.