Creating a Sum Column on a Table

P

Pete

I am trying to create a table that has 4 columns. The first three fields are
test scores and the last is a column that I wish to have automatically sum
the first three numbers. How does one accomplish this?
 
B

Beetle

The short answer is - you don't.

You should not be storing calculated values in a table. You should be doing
your calculations in a query or in a form control. Storing the sum in the
table is a waste of space, and you run the risk of the the calculated value
(which is stored in your table) being incorrect if the underlying data is
changed.

HTH
 
W

Wayne-I-M

Hi Pete

Create a new query based on the table.
In a blance column add something like this

Result:
Nz([TableName]![Field1],0)+Nz([TableName]![Field2],0)+Nz([TableName]![Field3],0)

Change the TableName and field names to what they really are.
The Nz function will replace a null with a 0 (which you need just in case
any other fields are empty.

In most case it's not a good idea to store calculated results as I you
change an of the data the result will be wrong.

in SQL ths would look like this
SELECT
Nz([TableName]![Field1],0)+Nz([TableName]![Field2],0)+Nz([TableName]![Field3],0) AS Result
FROM TableName;
 
P

Pete

Thanks alot for the prompt response. I feared that might be the case. Maybe
this is a better question...

I need to generate series of queries on from a much more complex table
relating to admission scores for college applicants. I need to provide my
coworkers and professors with access to the combined totals to these three
test scores in a variety of reports and queries. Am I correct in
understanding that outside of manually entering them into a column typed sum,
my only other option is to include an equation with every query or report I
generate?
 
P

Pieter Wijnen

Make one query & reference that in your other queries instead of the table

HtH

Pieter
 
B

Beetle

Well you certainly should not need to manually enter all the sum values as
you stated. You should just be able to create a query (or perhaps multiple
queries, depending on your circumstances) with a field that performs whatever
calculation you need. Then any reports you need could be based on this query
(or queries). You would not need to perform the calculation again in the
report, because the report gets the calculated value form the query.
 
P

Pete

Thanks. I think you all have given me the proper logic path to deal with
this. I appreciate it!
 
G

guest guest

I am trying to create a table that has 4 columns. The first three fields are
test scores and the last is a column that I wish to have automatically sum
the first three numbers. How does one accomplish this?
Click to expand...


hi
 
P

Pieter Wijnen

might want to throw in a couple of nz's
SELECT Table1.Col1, Table1.Col2, Table1.Col3,
nz([Col1],0)+nz([Col2],0)+nz([Col3],09 AS TotalCol
FROM Table1

HtH

Pieter

Michael Kintner said:
Table1
Col1 Col2 Col3 ColSum
1 2 3 x
2 4 5 x

Example1: Totals the three columns on the fly as value TotalCol

SELECT Table1.Col1, Table1.Col2, Table1.Col3, [Col1]+[Col2]+[Col3] AS
TotalCol
FROM Table1;

Example2: Actually updates the ColSum Field by totally Col1, Col2 and Col3

UPDATE Table1 SET Table1.ColSum = [Col1]+[Col2]+[Col3];


Hopefully that helps.
Michael Kintner
http://www.michaelkintner.com




guest said:
hi
Click to expand...
Click to expand...
 
Top