Use expression builder to total values in the Detail Section of Re

[LindaA]

I have a report based on a cross tab query which I have requested detail and
summary totals to SUM the values by each of the Rows: State. Columns: ST,
STE, APP, MAT, OPE, and WIP. The SUM of each of those columns is shown in
the footer. But, I would like to show the sum of each of the Rows in the
detail section. What is the expression that would be used to show the SUM of
each of these fields? Or, is there another method using the query itself to
do this task?

 

[Maurice]

Why don't you keep it simple. Add an additional field besides the last field
and in there place:

=sum([field]+[field2]+[field3]) etc...

Where the field refers to the fields you have placed in your report.
Works great, looks great and works simple..

hth

 

[John Spencer]

You can add a calculated field in the query

Field: Total: Nz(St,0)+Nz(STE,0)+Nz(App,0)+Nz(MAT,0)+Nz(OPE,0)+Nz(WIP,0)

The reason for using the Nz function is to handle any fields that are
Null (blank) and force them to return zero. If you attempt to add a
Null to anything else the result is Null. If your six fields always
have a value then you don't need to use the Nz function.

You could also use the above as a control's source in your report,
instead of adding it as a calculated field in the query

=Nz(St,0) + Nz(STE,0) + Nz(App,0) + Nz(MAT,0) + Nz(OPE,0) + Nz(WIP,0)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[LindaA]

Thank you, John and Maurice. That worked great in the control's source of
the text box for the detail records. However, now I want to sum the records
by the "State" footer. I've tried to place your expression in a new text box
on the footer and I get only the sum of the last detail record in the group.

 

[John Spencer]

I'm not quite sure what you want, but you can try the following

=Sum(Nz(St,0)+Nz(STE,0)+Nz(App,0)+Nz(MAT,0)+Nz(OPE,0)+Nz(WIP,0))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[LindaA]

I found the answer.

=Sum(Nz([St],0)+Nz([STE],0)+Nz([App],0)+Nz([MAT],0)+Nz([OPE],0)+Nz([WIP],0))