most close return

  • Thread starter Thread starter inungh
  • Start date Start date
I

inungh

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 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,
Click to expand...

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.
 
Give this a go

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



inungh said:
I would like have a query or method to get most close value like
following:
Click to expand...
Code       Value
Click to expand...
  A+         95
  A           90
  A-          85
Click to expand...
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.
Click to expand...
Is it possible to have a query or any function to return most close
value code?
Click to expand...
Your information is great appreciated,- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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

Thanks again,
 
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
 
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/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.
 
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










- Show quoted text -
Click to expand...
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


(...)
- Show quoted text -
Click to expand...
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:

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 -
Click to expand...

Thanks millions for helping,

Am I right?
when the grade is not many for many students then use nest iif.
If there is many grade (code) then use lookup table.

Thanks millions again,
 
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





- Show quoted text -
Click to expand...


inungh:

It is not the number of students we are considering. It is the number
of discrete grades possible. Duane is designing a system to handle an
unlimited number of grades and assumes that the grading criteria will
change frequently. I am assuming a limited number of grades and a
steady grading criteria.
 
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.
Click to expand...


I disagree.

Using a function (IIf) in a where condition prevents using
an index to optimize the query's performance. The query
that Michel posted should be faster than any expression.
For a very small number of groupings, it might not be much
faster, but I can't see how it could be "much" slower. IMO,
the additional flexibility and reduced maintenance costs
become the dominant considerations vs. a small performance
difference.
 
inungh said:
Am I right?
when the grade is not many for many students then use nest iif.
If there is many grade (code) then use lookup table.
Click to expand...


So far, the opinions are 3 to 1 against coming to that
conclusion.
 
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
'====================================================
 
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
'====================================================





- Show quoted text -
Click to expand...

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
 
Back
Top