Row Totaling

[magicalsue]

I was wondering if there is a better way to total a row other than using Col1 + Col2 +Col3 etc?

I have 65 columns in my table that I need to total across but the Expression Builder seems to struggle and miss some if I add them all in.

Is there a way simular to the Totals on column option?? or =SUM(B2R2) in excel?

Thanks

 

[Minty]

That sounds like you maybe haven't stored your data in a sensible (Access sensible) fashion.

Have you copied a spreadsheet layout into Access? This is why you are now struggling with doing what should be straight forwards.

Perhaps you could show a picture of your table design?

 

[jdraw]

You might be able to use vba with a For loop and the fields collection, but knowing the fields in your table would help.
Something along this line.

For i = 0 to rs.Fields.Count -1
Total = Total + rs.Fields(i)
Next i

 

[Mark_]

2nd to Minty's post.

Normally when you see multiple items that need to be totaled, they are child records of a parent. Say you are doing expenses. In a spread sheet you would have one column for each expense for a given person. You would then simply sum the range.

In a database, each expense would become a child record to the parent person. Instead of having 65 (or more) columns across, you get 65 (or more) rows in the child table that you can sum.

This means that when you add a new type of expense, instead of adding a column (or columns) you add a new "Expense type" and add child records only for those people who have that expense.

You may want to look up data normalization to get a better idea of what I'm talking about.

 

[Frothingslosh]

While jdraw is technically correct, I'm with Minty - I smell an underlying normalization problem that is only going to grow worse with time.

 

[magicalsue]

Thanks everyone .

I am not using access to create a growing database, I am using it to query and manipulate data from multiple sources (was using excel but found it less flexable and slower for some things).

I agree that to many columns are not sustainable, but luckily this is a rare thing

I can only send an example of my table but basically the table is grouped on Col1 then the remaining are summed. I want a total value horizontally for each in Col1 :

 

[isladogs]

I'm with all the previous replies in stating you should change to a normalised structure with few fields and many rows.
This will make the solution to your problem trivial (and simplify many other tasks)

If you do so, you can even make use of the built in totals row which is available in tables, queries etc.
See https://www.access-programmers.co.uk/forums/showthread.php?t=300211&highlight=Totals+row+table

 

[Mark_]

If you are stuck with this because of how the underlying data is presented, can you make a function that will return your total? Either by passing it a record ID and having it read the record and total OR by simply passing all values to total?