Column Sum

[JRB]

Hi, I'm using Access 2000. Ive got a table set up with a bunch of columns
that hold numbers. What I want is to add another column that holds the sum of
these columns. Is there any way to set this up in Access so that column
holding the sum will automatically update if one of the other values changes?
Thanks.

 

[Duane Hookom]

This is generally considere a bad idea. You can create a calculation in a
query, form, report, or code to create the sum so there isn't a need to
store the value. Also, summing across fields rather than across records
raises a red flag regarding normalization.

BTW: You can't stuff a total into a column when working directly with Access
tables. You could write code in a form that would total the other fields and
store the result.

 

[JRB]

Hi, thanks for the reply. I would like to use a query to sum the fields. How
would I do that? I'm not much of a SQL buff. Thanks.

 

[Duane Hookom]

Can you provide your table structure?

 

[JRB]

Hi, the table structure is simply 15 columns that are double's. Named "Week1"
to "Week15". Thanks.
I was thinking something like:

SELECT SUM(SELECT Week1, Week2, ... , Week15)
FROM Weeks;

 

[Duane Hookom]

Your table is un-normalized. Having fields like ...1, ...2, ...3, ....4 is
not generally a good way to structure your data. If you had a field named
"Wk" with values 1 - 15 and another field for your value, you could use sql
like:
SELECT Sum(theValue) as TheSum
FROM tblYourTable

If you can't fix your table structure, you can use an humungous expression
like:
SELECT Nz(Week1,0) + Nz(Week2,0) + Nz(Week3,0)+...
FROM Weeks;