Query design to search multiple ranges (1 Viewer)

TML15

Member
Local time
Today, 13:31
Joined
Apr 15, 2021
Messages
41
Hi everyone! I've been asked to create a query using Design View (not SQL) to compare addresses in a table to addresses in another table that are listed in ranges
and provide a list of addresses that match the ranges and a list that do not match the ranges.

1630521515901.png


So, these are examples of the address ranges. Each has a low end & high end to the range and each are either even numbers or odd numbers.

This 2nd list is the actual addresses to compare to see if they fit within one of the ranges.

1630521631443.png


I've been thinking of using the 'between' formula like this - Between [R_LADD] And [R_HADD] - but if there is more than 1 range,
then the query doesn't work. For example, 9 Agustus Av can be found in the 1st range of the odd numbers.

1630522182688.png


So, I believe that I can create a query that will find addresses that will match the ranges but when I try to create a query to find addresses that do
not match the ranges, the query includes the other ranges. For 9 Agustus Av that was already matched, it will also show in the list of unmatched
because of the 2nd range 19 to 41, ,20 to 36. If the address matches 1 of the ranges, I don't want it to check the others.

Would anyone have a suggestion on how to build these 2 queries - matched and unmatched in Design View (not SQL)?

Sorry if this is confusing!!!
Thanks in advance!



\\\
 

June7

AWF VIP
Local time
Today, 09:31
Joined
Mar 9, 2014
Messages
3,713
Common approach is like:

SELECT * FROM table WHERE NOT <some unique identifier> IN (SELECT <some unique identifier> FROM table WHERE <criteria that returns records that match>)
 

plog

Banishment Pending
Local time
Today, 12:31
Joined
May 11, 2011
Messages
10,651
First let's talk about your tables:

1. What's so magical about odd and even numbers that they need their own fields? Not to brag about my mathematical genius, but I can almost immediately discern any number's parity without pen and paper. They all need to go into the same field and if you need to know if they are even or odd you can do that in a query.

2. Your 4 range fields in the first table aren't numerical--they are text and you use actual numbers in the second table to compare to them. That's not going to end well. The computer is going to convert everything to text and compare them as text. When comparing text "81" is between "8" and "9" and "2319" is between "1" and "24". If you want to compare numbers, use numbers.

Also, never post the words "didn't work" without explaining what that means. Unexpected results? No results? Error message? Computer caught on fire and burned down the office? Tell us how it "didn't work".

My guess is that your example didn't produce any results. As I stated in #2 above "81" falls between "8" and "9". That also means "9" is not between "1" and "17". Only "numbers" starting with a "1" will meet that criteria because you are comparing strings. The solution to that is to compare numbers to numbers.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:31
Joined
May 21, 2018
Messages
5,686
Lets assume you fix the first table and make the range field numeric. My guess is that was what you meant to do. Then you need to add a unique ID field to the second table. Easiest is to add an autonumber.
Then If you make the first query as you stated, that query needs to include ID for all the records that fall in a range. Then do what @June7 said.
Lets assume you add an autonumber to the second table called "Add_ID". Lets assume your match query is called "qryMatchedAddresses" then your qryUnmatchedAddresses is something like

Select * from ActualAddresses where ADD_ID NOT IN (Select ADD_ID From qryMatchedAddresses)

 

June7

AWF VIP
Local time
Today, 09:31
Joined
Mar 9, 2014
Messages
3,713
1. Been a while since I've worked with GIS data but seem to remember this is common practice because odd and even building numbers are on opposite sides of street and an address geocoding tool might rely on this arrangement - locator style called US Address—Dual Ranges https://desktop.arcgis.com/en/arcma...htm#GUID-5B8C1F48-64DA-4A9B-B08C-3F0EBFEE8566

2. one would think fields should be number type but see the above referenced link
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
, 02:31
Joined
May 7, 2009
Messages
14,564
create first two queries (1 for each table you have to convert) the Numeric String to Real Number:

select *, Val(L_LAdd) As Low, Val(L_HAdd) As High from yourFirstTable; (query1)

select.* Val([st # odd] & [st # even]) As HausNo from your second table; (query2)

next, join the two (using HausNo from query2 and Low/High on Query1)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:31
Joined
Feb 19, 2002
Messages
33,681
I've been asked to create a query using Design View (not SQL)
Just FYI, SQL and Design view are just two different views. It doesn't matter which view you use to create the query, you can always toggle to the other view unless you create something in SQL view that cannot be represented in QBE view like a non equi-join. i.e. A join that uses something other than an equal sign.

Assuming this is an assignment for a class because if it isn't, your question makes no sense at all,
1. Open the query in design view and add both tables.
2. Join the two tables on the Address field and the Suffix (you have a column naming anomaly that needs fixing). Consistency is your friend.
3. On row one of the criteria add the expression to look at the left field
4. On row two of the criteria add the expression to look at the right field

The way the QBE works is that criteria on the same row are AND'd and criteria on different rows are OR'd. If you have a situation where your criteria is complex, you might have to repeat one of the expressions multiple times. For example if you had ro represent A OR (B and C) you would have A on line one and B on line one and then you would repeat A on line two and add C on line two.

One thing though, Access is parentheses crazy. When you switch from QBE view to SQL view, Access is going to add a bunch of unnecessary parentheses.
 
Last edited:

June7

AWF VIP
Local time
Today, 09:31
Joined
Mar 9, 2014
Messages
3,713
Strongly advise not to use spaces nor punctuation/special characters in naming convention.

Does 2nd table have unique autonumber ID field? If not, add one.

Query to get matches:

SELECT Data.ID, Data.STREET_NAME, Data.STREET_TYPE, Data.[st # odd], Data.[st # even]
FROM AddList INNER JOIN Data ON (Data.STREET_TYPE = AddList.SUFFIX) AND (AddList.NAME = Data.STREET_NAME)
WHERE (((Data.[st # odd]) Between Val([L_LADD]) And Val([L_HADD]))) OR (((Data.[st # even]) Between Val([R_LADD]) And Val([R_HADD])));

Query to get non-matches:

SELECT Data.* FROM Data WHERE ID Not IN (SELECT ID FROM Matches);

Or

SELECT Data.*
FROM Data LEFT JOIN Matches ON Data.ID = Matches.ID
WHERE (((Matches.ID) Is Null));
 
Last edited:

Users who are viewing this thread

Top Bottom