If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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, |
#12
|
|||
|
|||
most close return
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. |
#13
|
|||
|
|||
most close return
|
#14
|
|||
|
|||
most close return
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] |
#16
|
|||
|
|||
most close return
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 |
|
Thread Tools | |
Display Modes | |
|
|