Horizontal Sum? Can I? Can I??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TimT - 13 May 2005 14:28 GMT

I have numerous fields that need to summed in various ways.
Is there a function (or formula..) that will drive a query to sum a range
horizontally because my expressions are getting way to large....
Any suggestions?

Reply to this Message

Duane Hookom - 13 May 2005 15:23 GMT

This is a symptom of un-normalized table structures. Have you evaluated your
table structure according to good normalization practices?

Signature

Duane Hookom
MS Access MVP
--

>I have numerous fields that need to summed in various ways.
> Is there a function (or formula..) that will drive a query to sum a range
> horizontally because my expressions are getting way to large....
> Any suggestions?

Reply to this Message

John Spencer (MVP) - 15 May 2005 23:05 GMT

The only way I know to do this is to use some vba to build a custom function to
sum all the values you pass to it.
UNTESTED AIR CODE FOLLOWS:

Public Function GetSum(ParamArray Values())
Dim dblSum As Double

dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + CDbl(Values(i))
End If
Next i
GetSum = dblSum

End Function

You can call it in a query by using it like any built in function.

Field: AddUp: GetSum([Table1].[Fieldc],[Table2].[FieldF],...)

> I have numerous fields that need to summed in various ways.
> Is there a function (or formula..) that will drive a query to sum a range
> horizontally because my expressions are getting way to large....
> Any suggestions?

Reply to this Message