[AccessD] Query Question

You spotted my deliberate error then ;-) 

That's great Gustav. A 3rd option to work on. Not in work now until next
week but then I'll give the 3 options a run-out and see.

Thanks Drew, Stuart and Gustav. Truly great stuff as always.

-- 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 12:06
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Query Question
>
>
>Hi Andy
>
>That's right, I didn't read carefully, sorry. 
>However, to look up a series of values is just as simple as 
>looking up just one, using a filtered Cartesian (multiplying) join:
>
>  SELECT 
>    tblValuesToLookup.Id, 
>    tblValuesToLookup.Var, 
>    tblLookup.Result1, 
>    tblLookup.Result2
>  FROM 
>    tblLookup, 
>    tblValuesToLookup
>  WHERE 
>    (((Val(Nz((Select Max(RangeEnd) From tblLookup As tbl 
>Where tbl.RangeEnd < tblLookup.RangeEnd),0)))<=[Var]) 
>    AND 
>    ((tblLookup.RangeEnd)>[Var]));
>
>will return:
>
>Id	Var	Result1	Result2
>1	7	1500	1700
>2	17	2000	2500
>3	27	2350	2900
>4	13	1500	1700
>
>Note the difference from your sample ...
>
>/gustav
>
>>>> andy at minstersystems.co.uk 01-05-2008 10:57 >>>
>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
>
>