#### SQL query for "capping threshold"

`Suppose I want to add all the numbers in one column, with thefollowing caveat:All values should be capped at some high threshold level.As an example, suppose the threshold is 10000 and the numbers in thecolumn are 7500, 8500, 9500, 10500, 11500 and 12500.  The query shouldreturn 555000 (which is 7500 + 8500 + 9500 + 10000 + 10000 + 10000).Any help will be appreciated.Thanks,Bhat`
 0
kbhat
3/16/2007 6:19:52 PM
access 16762 articles. 3 followers.

3 Replies
749 Views

Similar Articles

[PageSpeed] 33

`SELECT Sum(IIf([YourField]>10000,10000,[YourField])) AS TheValueFROM tblCapped;Insert the right field and table names. Also I think that your calculation below has one too many zeros in it.-- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder."kbhat@kaxy.com" wrote:> Suppose I want to add all the numbers in one column, with the> following caveat:> All values should be capped at some high threshold level.> > As an example, suppose the threshold is 10000 and the numbers in the> column are 7500, 8500, 9500, 10500, 11500 and 12500.  The query should> return 555000 (which is 7500 + 8500 + 9500 + 10000 + 10000 + 10000).> > Any help will be appreciated.> > Thanks,> Bhat`
 0
Utf
3/16/2007 6:49:16 PM
`SELECT Sum(IIf([YourField]>10000,10000,[YourField])) AS TheValueFROM tblCapped;Insert the right field and table names. Also I think that your calculation below has one too many zeros in it.-- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder."kbhat@kaxy.com" wrote:> Suppose I want to add all the numbers in one column, with the> following caveat:> All values should be capped at some high threshold level.> > As an example, suppose the threshold is 10000 and the numbers in the> column are 7500, 8500, 9500, 10500, 11500 and 12500.  The query should> return 555000 (which is 7500 + 8500 + 9500 + 10000 + 10000 + 10000).> > Any help will be appreciated.> > Thanks,> Bhat`
 0
Utf
3/16/2007 6:49:36 PM
`I have a function I use for a variety of purposes that will work great for your purposes.  You can pass this function as many parameters as you want and it will return the minimum value.  When I have multiple date fields in a database and want to know the earliest date, I will use this. Occassionally, usually when I run into an unnormallized database, when I need to know the minimum number across several fields, I will use it also. With a little modification, you can convert this to give you the maximum value as well.In your case, you would just use it as a cumputed column in your query. Something like:Select SUM(Minimum(10000, [yourField])) as ThresholdSumFrom your tableHTHDale******************Public Function Minimum(ParamArray ArrayList()) As Variant    'Returns the minimum value from among the list of parameters passed    'Works on all data types    Dim lngLoop As Long    Dim LocalMin As Variant    For lngLoop = LBound(ArrayList) To UBound(ArrayList)        If IsNull(ArrayList(lngLoop)) Then            'skip to next array element        ElseIf IsEmpty(LocalMin) Then            LocalMin = ArrayList(lngLoop)        ElseIf ArrayList(lngLoop) < LocalMin Then            LocalMin = ArrayList(lngLoop)        End If    Next    Minimum = LocalMinEnd Function<kbhat@kaxy.com> wrote in message news:1174069192.613382.287850@d57g2000hsg.googlegroups.com...> Suppose I want to add all the numbers in one column, with the> following caveat:> All values should be capped at some high threshold level.>> As an example, suppose the threshold is 10000 and the numbers in the> column are 7500, 8500, 9500, 10500, 11500 and 12500.  The query should> return 555000 (which is 7500 + 8500 + 9500 + 10000 + 10000 + 10000).>> Any help will be appreciated.>> Thanks,> Bhat> `
 0
Dale
3/16/2007 10:21:10 PM