Summing across rows in a query

J

JoeA2006

I need to total 24 fields in a row as part of a query. Is there a simple way
to add these fields instead of creating an expression referencing each field?
 
M

Marshall Barton

JoeA2006 said:
I need to total 24 fields in a row as part of a query. Is there a simple way
to add these fields instead of creating an expression referencing each field?
Click to expand...


The fact that the columns can be added together implies that
the columns are related and that is a big NO NO is a
relational database. You should restructure your data into
multiple related normalized tables so you can use standard
query operations to sum the values.
 
J

John Spencer

If you can't change your data structure, you can use a VBA function to do
the calculation. You would still need to enter all the fields in the
arguments for the function, but you wouldn't have to worry about handling
null values with the NZ function.

Field: fRowSum(Field1,[Field 2], Field3,....,Field24)

There is an upper limit on the number of arguments (29) that you can use
within a query.

Public Function fRowSum(ParamArray Values()) As Variant
'====================================================================
' Procedure : fRowSum
' Created : 12/6/2006 09:08
' Author : John Spencer
' Purpose : Sum a group of numbers passed in,
' handles numbers and text strings that are all numeric
' or that can be interpreted as numbers (1e3 is 1000)
'====================================================================

Dim i As Integer, dSum As Variant

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dSum = dSum + Val(Values(i))
End If
Next i

fRowSum = dSum
End Function



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top