[AccessD] Query Question

Hi Gustav. Thanks for this but if I understand this right it's designed to
return just one value based on an input value passed in as a parameter. My
fault entirely if my original question wasn't clear but I need a query which
returns the correct lookup value for all records in my Table1. So if my
input is, say,

7
17
27
13

My output would be

7	1500	1700
17	2000	2500
27	2350	2900
13	2000	2500

Sorry if I misled you by over-simplifying my question.

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


>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
>Sent: 01 May 2008 08:43
>To: accessd at databaseadvisors.com
>Subject: Re: [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 
>
>
>
>-- 
>AccessD mailing list
>AccessD at databaseadvisors.com 
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>