analyze one table with another

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

I have two datasets--one has two sets of ranges and an id
number(text) -- the other has a text value that once
string analized leave two numbers-- these numbers have to
be analized in the first table -- to determine which
ranges they fall into:

Ranges in table one,
ID range1 Range2
ab 1-20 5-34
values in table two,

ID12-22 the result would be ab (the id number from table
one.)

What would be the best method of analyzing this data. I
have tried to write a function that would iterate through
but the datasets are huge. I can't wrap my head around the
query.

any help would be appreciated.
 
Hi,



I would start with a:



SELECT id
FROM tableone
WHERE( x BETWEEN range1_low AND range1_high)
AND ( y BETWEEN range2_low AND range2_high)


that assumes that tableone has five fields:

id, range1_low, range1_high, range2_low, range2_high

and that x and y are parameters. If x and y are from a table, table2:


SELECT x, y, Nz(id, "NO MATCH")
FROM tableone As a RIGHT JOIN table2 As b
ON ( b.x BETWEEN a.range1_low AND a.range1_high)
AND ( b.y BETWEEN a.range2_low AND a.range2_high)



Note that nothing in the design seems to forbid a (x, y) be part of TWO or
more RANGES (ie, range overlap).



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top