Auto calculate sum of multiple fields by record

[Guest]

Is there a way to use a query to sum totals of several fields per record? In
the db I have designed, I have 5 seperate fields for estimates from different
departments. There is also a field for Total estimates. What I would like to
do is have the Total Estimates field autocalculate the total sum of the other
5 estimate fields. Is this possible to do through a query? If so how? If not
- is it possible at all?

Thanks!

 

[Conan Kelly]

Tiffer,

Yes there is. But you wouldn't have the "Totals" field in your Table. That will be a calculated field in your query and it will be
recalc'ed every time the query is run.

If you open a new query in design mode and add the table that you want this calculated field for. Add all fields from the table to
the grid below. In one of the empty columns at the end, in the grid, you can type "Total: [name of 1st field to sum] + [name of
2nd field to sum] + [name of 3rd field to sum]...." (make sure you use the square brackets, just replace "name of 1st field..."
with your field names). Now you will have results with all of the fields that are in the table and a new "Total" field.

Hope this helps,

Conan Kelly

 

[Steve Schapel]

Tiffer,

Conan has given some first class advice on how to achieve what you asked
for.

However, this is quite irregular in a database. Normally you would not
have 5 fields for departmental estimates. Is your database design set
in concrete, or could you consider a revision? You will almost
certainly be better, and save yourself a lot of time down the track, if
you remove these 5 fields from your existing table, and make a separate
table, related many-to-one with the existing table, with a field for the
estimate and another field for the department. That way, the estimates
for this record will be in one field in 5 records iin the Estimates
table, instead of 5 fields in 1 record. This will make calculations
like you want much simpler.