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 |
#1
|
|||
|
|||
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, |
#2
|
|||
|
|||
most close return
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, |
#3
|
|||
|
|||
most close return
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. |
#4
|
|||
|
|||
most close return
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, |
#5
|
|||
|
|||
most close return
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-'. |
#6
|
|||
|
|||
most close return
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, |
#7
|
|||
|
|||
most close return
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, |
#8
|
|||
|
|||
most close return
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. |
#9
|
|||
|
|||
most close return
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. |
#10
|
|||
|
|||
most close return
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. |
|
Thread Tools | |
Display Modes | |
|
|