#Error while summing up the columns

[ria_arora]

When I summing up more than 100 columns then total is showing #Errorinstead of amount.

Select C1+C2+C3+C4+C5+...+C100

Till 100 columns sum is ok

Select C1+C2+C3+C4+C5+...+C160

but if I add another column then sum is showing as #Error instead of value.

Please let me know what is the solution for this?

Thanks
Ria

 

[JamesMcS]

Are there values in all the columns? You might have to Nz them all....

It may be that that's the maximum (I'm not sure to be honest) - what you might have to do is have one column (assuming this is in a query?) for values 1-99, then another for 100-160, then sum the two together in a third.

 

[ria_arora]

I really did not get you? Can you please throw some more light? Pls give me some example.

Thanks
Ria

 

[stopher]

Access isn't really designed for summing columns like this. That's not to say it can't do it, it's just totally inappropriate. You're better off using Excel for this kind of thing if you must keep the structure.

However, if you want to do this in Access then you need to change the structure by normalising your database. Then the task of summing is easily done with only a short query.

Chris

 

[ria_arora]

Hi Chris,

I can't change the design / normalising as this is implemented in other systems as well.

I need some work around for this.

I'm sure there will be some work around for this.

Thanks
Ria

 

[JamesMcS]

Hi - if you're summing up all those values, and one of them is null, it'll throw up the error you're getting. You have to do it like this:

SELECT Nz(C1,0)+Nz(C2,0)... AS SumofC

What I meant by the maximum thing is that I'm not sure how many values you can sum up in one field. You might have to have C1-C50 as SumOfC1, C51-C100 as SumofC2 and so on, then Your grand total would be SumofC1+SumofC2 etc.

I would disagree that Access isn't made for summing columns, I use it for summing up sales values for individual countries all the time.... and it works fine....

 

[DCrake]

If by any remote change you have a table with 100's of fields with names such as C1, C2,C99,etc I would definately look at normalising it. However if you do not have that pleasure then what you must do is to ensure that the default value in the field properties is set to 0 (zero) this will ensure that all unpopulated fields will not be null but have a zero value, which will prevent the #Error whilst summing.

 

[JamesMcS]

Very true, if there's any way you can have each C as a record rather than a column header that would be much better. And if you can ensure that the values aer all zero filled before you start summing up, then all good.

DC, would using recordsets and a loop be better for summing all this up? As in

for each field in Rst
if left(field.name)="C" then
Add to a variable
Next control
Update sum field with variable value

 

[DCrake]

If it was me I would export it to Excel and do the calcs there.

Even if you get it to work in Access the presentation of the data would be very difficult to do without looking like a complete mess.

 

[vbaInet]

What is the data type of those fields?

If they are all Number data type then you can write James' code like this

SELECT (Nz(C1) + Nz(C2) + ...) AS SumofCs FROM TableName

 

[stopher]

What is the data type of those fields?

If they are all Number data type then you can write James' code like this

As the OP says, the problem seems to occur when the column name exceeds C100 (I just tested). However, as James suggests, doing its as two seperate calculations/columns (1-100 and 101-160) seems to work find. But I've no idea why.

Chris

 

[vbaInet]

As the OP says, the problem seems to occur when the column name exceeds C100 (I just tested). However, as James suggests, doing its as two seperate calculations/columns (1-100 and 101-160) seems to work find. But I've no idea why.

Chris

Ah, I didn't notice that If the column names aren't really that short, i.e. C1, C2 etc then it may have something to do with this:

Number of characters in a cell in the query design grid         1,024

 

[DCrake]

Then there you are 1024 is a magic number 1Kb must be the limit

 

[stopher]

Very true, if there's any way you can have each C as a record rather than a column header that would be much better. And if you can ensure that the values aer all zero filled before you start summing up, then all good.

DC, would using recordsets and a loop be better for summing all this up? As in

for each field in Rst
if left(field.name)="C" then
Add to a variable
Next control
Update sum field with variable value

As James suggested...here's a function to sum up a specified range of columns (assuming there are no other columns interlaced) and put the sum in a specified column (since we're not normalised ):

Public Sub SumCols(tblName As String, startCol As String, endCol As String, resultCol As String)

Dim db As Database
Dim rs As DAO.Recordset
Dim vartotal As Variant
Dim i As Integer

Set db = CurrentDb()

Set rs = db.OpenRecordset(tblName)

With rs
    startPosition = rs.Fields(startCol).OrdinalPosition
    endPosition = rs.Fields(endCol).OrdinalPosition
    vartotal = 0
    While Not .EOF
        For i = startPosition To endPosition
            vartotal = vartotal + .Fields(i)
        Next i
        .Edit
        .Fields(resultCol) = vartotal
        .Update
        .MoveNext
    Wend
End With

Set rs = Nothing
Set db = Nothing

End Sub

And call it with:

Public Sub test()

Call SumCols("tblTest", "c1", "c160", "SumTotal")

End Sub

 

[ria_arora]

Thanks to all.

I have splitted the columns into two columns. That's the only workaround works.

Thanks a lot for good suggestions.

Regards
Ria