Prev: Help to select record by checking multiple child items
Next: Query Too Complex
From: louisjohnphillips on 4 Jul 2008 16:39
On Jul 4, 1:08 pm, John Spencer <spen...(a)chpdm.umbc> wrote:
> I don't think you are correct. First, you can only nest 7 levels of IIF,
> so that is not a solution that will work for more than a few values.
>
> You can use the table solution and if you have indexes on the proper
> fields, it will be just as fast as any other method.
>
> '====================================================
>   John Spencer
>   Access MVP 2002-2005, 2007-2008
>   The Hilltop Institute
>   University of Maryland Baltimore County
> '====================================================
>
>
>
> louisjohnphill...(a)gmail.com wrote:
> > On Jul 4, 10:19 am, Duane Hookom <duanehookom(a)NO_SPAMhotmail.com>
> > wrote:
> >> I don't care for any solution that hard-codes data. Data belongs in your
> >> tables not in your code. Readhttp://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx.
>
> >> A small lookup table provides the greatest flexibility. Your table could
> >> have the min and max values with the appropriate code. My second choice would
> >> be to create a small user-defined function that accepts the value and returns
> >> the code. I wouldn't even consider using nested IIf()s or the Switch()
> >> function.
>
> >> --
> >> Duane Hookom
> >> Microsoft Access MVP
>
> >> "inungh" wrote:
> >>> I would like have a query or method to get most close value like
> >>> following:
> >>> Code       Value
> >>>   A+         95
> >>>   A           90
> >>>   A-          85
> >>> I would like to have the query return A+ if value is greater or equals
> >>> than 95 and return A when the value between 90 and 95.
> >>> Is it possible to have a query or any function to return most close
> >>> value code?
> >>> Your information is great appreciated,- Hide quoted text -
> >> - Show quoted text -
> > Duane:
>
> > You are correct.  A small lookup table provides the greatest
> > flexibility.  However, performing the lookup requires a join of the
> > tables.  If you have many, many students and few distinct grade
> > groupings, you should consider the iif().   It will provide you with
> > much better performance.- Hide quoted text -
>
> - Show quoted text -

John:

This is not worth discussing further.

I only wish to alert you that my version of Access allowed this SQL.
Therefore, no matter how silly the query is, the limit is not a
nesting of seven.


SELECT iif( Group_Member_Count > 13, '13 persons',
iif( Group_Member_Count > 12, '12 persons',
iif( Group_Member_Count > 11, '11 persons',
iif( Group_Member_Count > 10, '10 persons',
iif( Group_Member_Count > 9, '9 persons',
iif( Group_Member_Count > 8, '8 persons',
iif( Group_Member_Count > 7, '7 persons',
iif( Group_Member_Count > 6, '6 persons',
iif( Group_Member_Count > 5, '5 persons' ,
iif( Group_Member_Count > 4, '4 persons',
iif( Group_Member_Count > 3, '3 persons',
iif( Group_Member_Count > 2, '2 persons',
iif( Group_Member_Count > 1, '1 persons'
) ) ) ) ) ) ) ) ) ) ) ) )
FROM Groups

First  |  Prev  | 
Pages: 1 2 3 4
Prev: Help to select record by checking multiple child items
Next: Query Too Complex