Prev: Help to select record by checking multiple child items
Next: Query Too Complex
From: inungh on 4 Jul 2008 11:34
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,

From: scubadiver on 4 Jul 2008 11:52

Give this a go

expr1: IIF([value]>=95, "A+",IIF(90=<[value]<95,"A","A-"))



"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,
>
>
From: louisjohnphillips on 4 Jul 2008 11:58
On Jul 4, 8:34 am, inungh <inu...(a)gmail.com> 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,

For completeness, please test this algorithm

select iif( value >= 95, 'A+',
iif( value >= 90, 'A',
iif( value >=85, 'A-', 'Unrated' ))) as Code
from MyGradeTable;

It should test for values 95 and above and assign those an 'A+'.
Failing to find a value of 95 and above, it should go on to the next
test of 90 and above. Failing to find a value of 90 and above, it
should test for 85 and above. If the value does not meet any of the
criteria, the code of 'Unrated' will be assigned.
From: inungh on 4 Jul 2008 12:00
On Jul 4, 11:52 am, scubadiver <scubadi...(a)discussions.microsoft.com>
wrote:
> Give this a go
>
> expr1: IIF([value]>=95, "A+",IIF(90=<[value]<95,"A","A-"))
>
>
>
> "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 -

Thanks for the message,
I just need continue to add for B+, B, B-, C+....etc.
am I right?

Thanks again,
From: louisjohnphillips on 4 Jul 2008 12:11
On Jul 4, 9:00 am, inungh <inu...(a)gmail.com> wrote:
> On Jul 4, 11:52 am, scubadiver <scubadi...(a)discussions.microsoft.com>
> wrote:
>
>
>
>
>
> > Give this a go
>
> > expr1: IIF([value]>=95, "A+",IIF(90=<[value]<95,"A","A-"))
>
> > "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 -
>
> Thanks for the message,
> I just need continue to add  for B+, B, B-, C+....etc.
> am I right?
>
> Thanks again,- Hide quoted text -
>
> - Show quoted text -

Yes. I'm not sure how far the iif can be nested, but I expect you
will not grade below 'F-'.
 |  Next  |  Last
Pages: 1 2 3 4
Prev: Help to select record by checking multiple child items
Next: Query Too Complex