most close return
in [Queries]
|
From: inungh on 4 Jul 2008 14:42 On Jul 4, 1:35 pm, louisjohnphill...(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.- 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,
From: louisjohnphillips on 4 Jul 2008 15:28 On Jul 4, 11:42 am, Duane Hookom <duanehookom(a)NO_SPAMhotmail.com> 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 > > > > "louisjohnphill...(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.- 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.
From: Marshall Barton on 4 Jul 2008 15:35 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. >> >> >> "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]
From: Marshall Barton on 4 Jul 2008 15:45 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]
From: John Spencer on 4 Jul 2008 16:08 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 '==================================================== 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 |