sum function

O

opeyemi1

Hello all:

I have an access table with about 52 columns.

I want to set up a query to add the values in column 3 to the end. I
the design view in the Field section, I tried to "build" an expressio
sum(column3:column52), but I am getting an error message. I also trie
to add each column individually, but there is not enough room. Pleas
help.

Thanks.

Opeyemi
 
S

Steve Schapel

Opeyemi1,

This is an irregular request. Your need to do this almost certainly
indicates a problem with your table design. If you can post back with
some more information about the data that is in these fields which you
want to add, maybe with examples, someone will be able to advise
accordingly.
 
J

John Spencer (MVP)

So you want to add column3, column4, ... Column51, column52 of each row.

The only real way I can see to do this is to write a custom function and use it.
Even then, you may run into the limits on the length of the string you can
enter in a cell of the query grid.

AIR CODE FOLLOWS - no error checking and returns zero if all values are null.

'------------- Code Starts --------------
Public Function fGetSumNumber(ParamArray Values()) As Variant
'
Dim i As Integer, dblSum As Double

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

fGetSumNumber = dblSum

End Function
'------------- Code Ends --------------

Too use that put the following in your grid cell

Field: TheTotal: fGetSumNumber([Column3],[Column4], ..., [Column52])

But Steve Schapel is correct, this problem is probably caused by a misdesign of
your table structure. You probably should have a table that looks roughly like

Some Id Field(s) - probably equivalent to what is in columns 1 and 2
TypeOfColumn - probably the name you have given to columns 3 to 52
Amount - the number you are currently entering in columns 3 to 52

Then to get the sum would be a trivial totals query..

Select Col1, Col2, Sum(Amount) as TotalAmount
FROM TheTable
GROUP BY Col1, Col2
 
Top