Totals in reports from calculated controls

[Guest]

I have controls in my report based on calculations and need to get the total
of the line items in the report footer. I don't seem to be able to refer to a
calculated control using the SUM function as I would normally, i.e.
=SUM([Calculated Field]) is there a way of doing this or can Access only SUM
fields named in the underlying SQL statement? Help much appreciated

 

[AlCamp]

Shiela,
You can't apply aggregate functions to calculated ("unbound") fileds in a
report.

Using an example of Price*Qty = ExtendedPrice, and you'd like to add up
ExtendedPrice in a footer...

In the query behind the report, create a column with...
ExtendedPrice : Price * Qty
Now, ExtendedPrice is a "bound" field. Place the "bound" fields Price,
Qty, and ExtendedPrice on your report.
In the appropriate footer, Sum(ExtendedPrice) will now sum up properly.
hth
Al Camp

I have controls in my report based on calculations and need to get the
total
of the line items in the report footer. I don't seem to be able to refer
to a
calculated control using the SUM function as I would normally, i.e.
=SUM([Calculated Field]) is there a way of doing this or can Access only
SUM
fields named in the underlying SQL statement? Help much appreciated

 

[Guest]

Hi Al, thanks for help, the problem I have is that some of the underlying
fields in the query use IIF statements to generate value and when I try to
create a new column based on that says can't do it as based on a sub query.
So I hoped I could do directly in report. Any ideas would be much appreciated
Sheila

Shiela,
You can't apply aggregate functions to calculated ("unbound") fileds in a
report.

Using an example of Price*Qty = ExtendedPrice, and you'd like to add up
ExtendedPrice in a footer...

In the query behind the report, create a column with...
ExtendedPrice : Price * Qty
Now, ExtendedPrice is a "bound" field. Place the "bound" fields Price,
Qty, and ExtendedPrice on your report.
In the appropriate footer, Sum(ExtendedPrice) will now sum up properly.
hth
Al Camp

I have controls in my report based on calculations and need to get the
total
of the line items in the report footer. I don't seem to be able to refer
to a
calculated control using the SUM function as I would normally, i.e.
=SUM([Calculated Field]) is there a way of doing this or can Access only
SUM
fields named in the underlying SQL statement? Help much appreciated

 

[AlCamp]

Shiela,
Whatever you used to get a value (you say it's an Iff statement to get
Price)... then just use that instead of the value.
Using my previous example...
ex. instead of Price * Qty = ExtendedPrice
use (your IIF statement here) * Qty = ExtendedPrice

Once you create a "bound" field for ExtendedPrice, it's a snap to add it
up in any footer.
hth
Al Camp

Hi Al, thanks for help, the problem I have is that some of the underlying
fields in the query use IIF statements to generate value and when I try to
create a new column based on that says can't do it as based on a sub
query.
So I hoped I could do directly in report. Any ideas would be much
appreciated
Sheila

Shiela,
You can't apply aggregate functions to calculated ("unbound") fileds
in a
report.

Using an example of Price*Qty = ExtendedPrice, and you'd like to add
up
ExtendedPrice in a footer...

In the query behind the report, create a column with...
ExtendedPrice : Price * Qty
Now, ExtendedPrice is a "bound" field. Place the "bound" fields
Price,
Qty, and ExtendedPrice on your report.
In the appropriate footer, Sum(ExtendedPrice) will now sum up
properly.
hth
Al Camp

I have controls in my report based on calculations and need to get the
total
of the line items in the report footer. I don't seem to be able to
refer
to a
calculated control using the SUM function as I would normally, i.e.
=SUM([Calculated Field]) is there a way of doing this or can Access
only
SUM
fields named in the underlying SQL statement? Help much appreciated