Grand Total in a report

  • Thread starter Thread starter Gary.
  • Start date Start date
G

Gary.

I have a small problem

Controls are
[UnitPriceAdjustment]
[UnitCountAdjustment]
[UnitsReceived]
[UnitsSold]
[SubTotal]

The [UnitPriceAdjustment] and the [UnitCountadjustment] are in the group
product header
The [UnitsReceived] and [UnitsSold] controls are in the detail section of
the report
The [SubTotal] control is in the group footer

In the group footer I have text boxes where I sum the [UnitsReceived] and
[UnitsSold] controls

Then I have added one more text box called [Subtotal] where I Subtract the
(([UnitsSold] from the [UnitsReceived]) and add [UnitCountAdjustment]) then
mulptly by [UnitPriceAdjustment]

Where I am having a problem is doing a grand total of the calaculated field
[Subtotal] in the report footer

Any help world be helpful
 
This is the error I get

Cannot have aggregate function in expression (Sum(Sum(nz9[Units
Received],0))*[UnitPrice]))

If I try to sum the controls in the footer I get nothing



Tom van Stiphout said:
You need to repeat the expression. Something like:
Sum((a-b+c)*d)

-Tom.
Microsoft Access MVP

I have a small problem

Controls are
[UnitPriceAdjustment]
[UnitCountAdjustment]
[UnitsReceived]
[UnitsSold]
[SubTotal]

The [UnitPriceAdjustment] and the [UnitCountadjustment] are in the group
product header
The [UnitsReceived] and [UnitsSold] controls are in the detail section of
the report
The [SubTotal] control is in the group footer

In the group footer I have text boxes where I sum the [UnitsReceived] and
[UnitsSold] controls

Then I have added one more text box called [Subtotal] where I Subtract the
(([UnitsSold] from the [UnitsReceived]) and add [UnitCountAdjustment])
then
mulptly by [UnitPriceAdjustment]

Where I am having a problem is doing a grand total of the calaculated
field
[Subtotal] in the report footer

Any help world be helpful
Click to expand...
Click to expand...
 
Try the following. You can only use actual fields in the calculation and not
refer to controls on the report.

=Sum((Nz([UnitsReceived],0)-Nz([UnitsSold],0)+ Nz(UnitCountAdjustment,0))*
[UnitPriceAdjustment])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Gary said:
This is the error I get

Cannot have aggregate function in expression (Sum(Sum(nz9[Units
Received],0))*[UnitPrice]))

If I try to sum the controls in the footer I get nothing



Tom van Stiphout said:
You need to repeat the expression. Something like:
Sum((a-b+c)*d)

-Tom.
Microsoft Access MVP

I have a small problem

Controls are
[UnitPriceAdjustment]
[UnitCountAdjustment]
[UnitsReceived]
[UnitsSold]
[SubTotal]

The [UnitPriceAdjustment] and the [UnitCountadjustment] are in the group
product header
The [UnitsReceived] and [UnitsSold] controls are in the detail section of
the report
The [SubTotal] control is in the group footer

In the group footer I have text boxes where I sum the [UnitsReceived] and
[UnitsSold] controls

Then I have added one more text box called [Subtotal] where I Subtract the
(([UnitsSold] from the [UnitsReceived]) and add [UnitCountAdjustment])
then
mulptly by [UnitPriceAdjustment]

Where I am having a problem is doing a grand total of the calaculated
field
[Subtotal] in the report footer

Any help world be helpful
Click to expand...
Click to expand...
Click to expand...
 
I didn't say Sum(Sum()). One Sum is enough. Access knows it is in the
footer so the scope is all data.

-Tom.
Microsoft Access MVP

This is the error I get

Cannot have aggregate function in expression (Sum(Sum(nz9[Units
Received],0))*[UnitPrice]))

If I try to sum the controls in the footer I get nothing



Tom van Stiphout said:
You need to repeat the expression. Something like:
Sum((a-b+c)*d)

-Tom.
Microsoft Access MVP

I have a small problem

Controls are
[UnitPriceAdjustment]
[UnitCountAdjustment]
[UnitsReceived]
[UnitsSold]
[SubTotal]

The [UnitPriceAdjustment] and the [UnitCountadjustment] are in the group
product header
The [UnitsReceived] and [UnitsSold] controls are in the detail section of
the report
The [SubTotal] control is in the group footer

In the group footer I have text boxes where I sum the [UnitsReceived] and
[UnitsSold] controls

Then I have added one more text box called [Subtotal] where I Subtract the
(([UnitsSold] from the [UnitsReceived]) and add [UnitCountAdjustment])
then
mulptly by [UnitPriceAdjustment]

Where I am having a problem is doing a grand total of the calaculated
field
[Subtotal] in the report footer

Any help world be helpful
Click to expand...
Click to expand...
Click to expand...
 
Back
Top