Query for values from 2 tables where one falls in a range calculated from values in one table.

Question

I have 2 tables. The first one has the columns name, value and offset. The second one has the columns result and calculation. I want to know if there is a way to write a query in access where I would select name, value, result and calculation on the criteria the selected result would be between the selected value+(offset/2000) and value-(offset/2000). The relation would be many to many.

Answer 1

Just join the tables.

SELECT t1.name, t1.value, t2.result, t2.calculation
FROM   table1 t1, table2 t2
WHERE  t2.result BETWEEN t1.value + (t1.offset / 2000)
                     AND t1.value - (t1.offset / 2000);

Or you can use the absolute value function.

SELECT t1.name, t1.value, t2.result, t2.calculation
FROM   table1 t1, table2 t2
WHERE  Abs(t2.result - t1.value) <= t1.offset / 2000;