most close return

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,

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,

On Jul 4, 8:34*am, 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,

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.

On Jul 4, 11:52*am, scubadiver
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,

On Jul 4, 9:00*am, inungh wrote:
On Jul 4, 11:52*am, scubadiver
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-'.

Define a table, Scores, with the fields:

FromThis ToThis Score
95 101 A+
90 95 A
85 90 A-
.... as data.




SELECT myScore, Scores.Score
FROM myTable INNER JOIN Scores
ON myTable.myScore = Scores.FromThis
AND myTable.myScore Scores.ToThis


is a query (in SQL view) which will translate you numerical scores into
coded ones. What is nice with that solution is that you can change the
ranges without changing any CODE (VBA or SQL code),since the data live where
data should live, in a table. You want add a range, add a record; delete a
merge, delete a record (and adjust the FromThis and ToThis of some other
record to effectively merge the ranges).



Vanderghast, Access MVP



"inungh" wrote in message
...
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,

I don't care for any solution that hard-codes data. Data belongs in your
tables not in your code. Read
http://weblogs.sqlteam.com/jeffs/arc...2/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,

On Jul 4, 10:19*am, Duane Hookom
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.

I doubt the iif performance will perceptibly outmatch the join. After all,
the iif will be always sequential, like an IN( list), such that the F-
requiring more time to be reached than the A+ (F- being the last one of the
list, while the A+ being the first one on the list), as example, while the
join will reach any range in the same amount of time than for the A+ (if
using indexes, if using a table scan, will have the same 'problem' than the
nested iif-s). Any difference in time is unlikely perceptible, for a human
(and probably in favor of the join, anyhow). So, with a tie, we have the
possibility to look for other criteria, such as maintenance. And
add/removing/merging range, with nested iif, is surely NOT as easy as doing
that in a table. And again, the user don't have to become a developer to
'change' the code to perform that otherwise 'administrative' modification.


Vanderghast, Access MVP


wrote in message
...
(...)

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.

I wouldn't be concerned with performance. I would be concerned with
maintainability. SQL performance is generally very good with the proper
indexes.
--
Duane Hookom
Microsoft Access MVP


" wrote:

On Jul 4, 10:19 am, Duane Hookom
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.