Sum totals not showing up in Queries or Reports

  • Thread starter Thread starter NewAccessUser
  • Start date Start date
N

NewAccessUser

Hi, new user here. I have created a table, which have fields for different
travel expenses. The last field is a total, and while in the design view of a
new form, I went to the properties of that "total" field and made the Control
Source to be the sum of the corresponding 6 fields I want summed. It DOES
produce the correct sum when shown on the form, however when I run a query or
make a report, the sum value doesn't show up when that field is listed. It is
blank. What am I doing wrong?
 
Hi, new user here. I have created a table, which have fields for different
travel expenses. The last field is a total, and while in the design view of a
new form, I went to the properties of that "total" field and made the Control
Source to be the sum of the corresponding 6 fields I want summed. It DOES
produce the correct sum when shown on the form, however when I run a query or
make a report, the sum value doesn't show up when that field is listed. It is
blank. What am I doing wrong?
Click to expand...

That is correct. It is not stored. You are attempting to improperly
use Access.
If you made the control source of a control bound to [Total] into an
expression, such as
=[FieldA] + FieldB]
how would Access know which field to store the data in as it is no
longer bound to [Total]?
Storing calculated data goes against the rules of database design.
Calculated data need not be stored in a table.
Anytime you need the result of a calculation, simply re-calculate it,
on a form, in a report, or in a query.
You can delete that field named "Total" from your table.
 
The value is not stored when you total the fields in a control on a form.

The way to handle this is to copy the formula you are using in the
control's source and paste that into the query (less the = sign). Then
the value will be calculated when the query executes. You should NOT
store the calculated value in a table - it can become wrong if you
should edit one of the six fields and forget to edit the sum.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top