Grand Total in a report

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
 
T

Tom van Stiphout

On Mon, 17 May 2010 09:57:56 -0700, "Gary." <[email protected]>
wrote:

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

-Tom.
Microsoft Access MVP
 
G

Gary Hull

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
 
J

John Spencer

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
 
T

Tom van Stiphout

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top