most close return

On Jul 4, 1:35*pm, 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.- Hide quoted text -

- Show quoted text -

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,

On Jul 4, 11:42*am, Duane Hookom
wrote:
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.- Hide quoted text -

- Show quoted text -


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.

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.


"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 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.

--
Marsh
MVP [MS Access]

inungh wrote:
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.


So far, the opinions are 3 to 1 against coming to that
conclusion.

--
Marsh
MVP [MS Access]

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
'================================================= ===


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.

On Jul 4, 1:08*pm, John Spencer 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
'================================================= ===



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.- 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