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 |

3/16/2007 6:19:52 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 |

3/16/2007 6:49:16 PM

0 |

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 |

3/16/2007 10:21:10 PM

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 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, Microsof...