most close return
in [Queries]
|
From: Duane Hookom on 4 Jul 2008 13:19 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. -- 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, > >
From: Michel Walsh on 4 Jul 2008 13:16 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" <inungh(a)gmail.com> wrote in message news:5cb446d0-36b4-4846-a0e2-2b8f6f33b439(a)z66g2000hsc.googlegroups.com... >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, >
From: louisjohnphillips on 4 Jul 2008 13:35 On Jul 4, 10:19 am, Duane Hookom <duanehookom(a)NO_SPAMhotmail.com> 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.
From: Michel Walsh on 4 Jul 2008 14:32 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 <louisjohnphillips(a)gmail.com> wrote in message news:2acb886f-aed2-45fd-91da-d33ebc25aaf2(a)z16g2000prn.googlegroups.com... (...) > > > 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.
From: Duane Hookom on 4 Jul 2008 14:42 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 "louisjohnphillips(a)gmail.com" wrote: > On Jul 4, 10:19 am, Duane Hookom <duanehookom(a)NO_SPAMhotmail.com> > 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. > >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: Help to select record by checking multiple child items Next: Query Too Complex |