[AccessD] Query Question

Hi Andy

This can be done returning all fields with one subquery only.

Given tblLookup like:

RangeEnd	Result1	Result2
15	1500	1700
25	2000	2500
100	2350	2900

the query could be build this way:

  PARAMETERS 
    Var Long;
  SELECT 
      Val(Nz((Select Max(RangeEnd) From tblLookup As tbl 
      Where tbl.RangeEnd < tblLookup.RangeEnd),0)) AS 
    RangeMin, 
      tblLookup.RangeEnd AS 
    RangeMax, 
    Result1, 
    Result2
  FROM 
    tblLookup
  WHERE 
    (((Val(Nz((Select Max(RangeEnd) From tblLookup As tbl Where tbl.RangeEnd < tblLookup.RangeEnd),0)))<=[Var]) 
    AND 
    ((tblLookup.RangeEnd)>[Var]));

Note that you will only have to specify the max. range (the min. is implicit). For this case it means that you must deal with a limited upper value, here 100. For an "unlimited" upper limit, insert some much higher value.

/gustav

>>> andy at minstersystems.co.uk 30-04-2008 17:30 >>>
Hi folks
Looking for best solution to a simple problem. It's of the "if var is
anything between x and y return this value, if anywhere between xx and yy
return that etc". I'll express it in its most basic form:

Table LKUP has fields
[RangeStart], [RangeEnd], [Result1], [Result2] and looks like this

0   14.9  1500  1700
15  24.9  2000  2500
25  99.9  2350  2900

([RangeEnd] is probably superfluous but I hope it makes the example clearer)

Table1 has (among others) field [LookupValue]

I want a query which if [LookupValue] on Table1 is anywhere between 0 and
14.9 returns 1500 and 1700 from Lkup, if it's between 15 and 24.9 returns
2000 and 2500, and anything from 25 upwards returns 2350 and 2900.

My solution is this:
SELECT Table1.[LookupValue],Max([Lkup].Result1) ,Max([Lkup].Result2)
FROM Table1 LEFT JOIN [Lkup] ON Table1.[LookupValue] >= [Lkup].[RangeStart]
GROUP BY Table1.[LookupValue];

Seems to work but because it uses Max it depends on the values in Result1
and Result2 getting larger as the lookups get larger. If they didn't then
the Max would return the wrong results (I think).

Any other, or better, ideas?

--
Andy Lacey
http://www.minstersystems.co.uk