calculated control in reports

  • Thread starter Thread starter shank
  • Start date Start date
S

shank

I have a calculated control in the detail section [TotalProfit]
That works fine. It's based on a fields being added etc. No problem.

I'm trying to put another calculated control in the Page Header section.
What I want to represent is: =Sum([TotalProfit])
All I get is an error.
How can I get this accomplished?
thanks!
 
I don't know of any method to sum values into a Pge Header section. There
are a couple recent threads regarding totals in Page Footers. You can never
use
=Sum([ControlFromOtherSection])
If the ControlFromOtherSection has a Control Source of
=(FieldB-FieldC)/FieldA
you can use
=Sum( (FieldB-FieldC)/FieldA )
 
That's what I was trying to do. However, I also tried your suggestion.

1st Control: TotalProfit =[SellPrice]-[TotalCosts]
Then I tried
2nd Control: Total =Sum([SellPrice]-[TotalCosts])
Still got the error.
I guess I will have to use a query and attack the problem that way.
thanks!

Duane Hookom said:
I don't know of any method to sum values into a Pge Header section. There
are a couple recent threads regarding totals in Page Footers. You can never
use
=Sum([ControlFromOtherSection])
If the ControlFromOtherSection has a Control Source of
=(FieldB-FieldC)/FieldA
you can use
=Sum( (FieldB-FieldC)/FieldA )

--
Duane Hookom
MS Access MVP


shank said:
I have a calculated control in the detail section [TotalProfit]
That works fine. It's based on a fields being added etc. No problem.

I'm trying to put another calculated control in the Page Header section.
What I want to represent is: =Sum([TotalProfit])
All I get is an error.
How can I get this accomplished?
thanks!
Click to expand...
Click to expand...
 
May said:
Putting sum or count in the page or report header absoutly
not work. The report has not gone though all the record,
how the program add up the data for you. Think about the
count function, how the program in page header know how
many record will be in detail section!!!
Click to expand...

The aggregate functions work just as well in the report
header section as they do in the report footer. Showing a
grand total in the page header or page footer is just a
matter of referring to a report header text box that uses
the aggregate function. I think this is what Shank is
trying to do.

However, showing a page subtotal in the page footer is a
little tricky and showing it in the page header is very
complicated.
 
Marshall Barton said:
The aggregate functions work just as well in the report
header section as they do in the report footer. Showing a
grand total in the page header or page footer is just a
matter of referring to a report header text box that uses
the aggregate function. I think this is what Shank is
trying to do.

However, showing a page subtotal in the page footer is a
little tricky and showing it in the page header is very
complicated.
Click to expand...

Thanks to all that's trying to help here, but I'm still lost. I changed a
couple things. I changed the report's control source to a query. I'd like
the report detail section to resemble the looks of a spreadsheet. Imagine I
have the following columns...

[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 1
[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 2
[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 3
[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 4
[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 5
.............................................................................
.......PROFIT = Sum([SellPrice]-Sum([ABCFees]) <-- Report Row 6
How do I make my report look like the above? I was originally trying to put
the PROFIT row in the header. That didn't work. Didn't work in the footer
either. When I put it in the detail section, it's repeated over and over for
each group. I'd be happy if I could get it under the last column like above
and shown "one-time" in the report. I know it's possible. I'm missing
something simple here I'm sure.
thanks!
 
shank said:
Thanks to all that's trying to help here, but I'm still lost. I changed a
couple things. I changed the report's control source to a query. I'd like
the report detail section to resemble the looks of a spreadsheet. Imagine I
have the following columns...

[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 1
[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 2
[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 3
[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 4
[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 5
............................................................................
......PROFIT = Sum([SellPrice]-Sum([ABCFees]) <-- Report Row 6
How do I make my report look like the above? I was originally trying to put
the PROFIT row in the header. That didn't work. Didn't work in the footer
either. When I put it in the detail section, it's repeated over and over for
each group. I'd be happy if I could get it under the last column like above
and shown "one-time" in the report.
Click to expand...

I don't understand your example, using Sum in the detail
section doesn't make sense to me. Shouldn't you be using
something more like:

AFees ... BFees ... CFees ... (AFees + BFees + CFees) ...
SellPrice ... (SellPrice - txtFees)

and the report footer:

=Sum([SellPrice]-Sum(AFees + BFees + CFees)

where AFees + BFees + CFees are fields in the report' record
source table/query. I didn't see what your ABCFees is
supposed to be, so I'm just guessing about that.
 
Marshall Barton said:
shank said:
Thanks to all that's trying to help here, but I'm still lost. I changed a
couple things. I changed the report's control source to a query. I'd like
the report detail section to resemble the looks of a spreadsheet. Imagine I
have the following columns...

[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 1
[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 2
[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 3
[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 4
[AFees] ... [BFees] ... [CFees] ... (Sum([ABCFees]) ... [SellPrice] ...
([SellPrice]-Sum([ABCFees]) <-- Report Row 5
...........................................................................
Click to expand...
..
......PROFIT = Sum([SellPrice]-Sum([ABCFees]) <-- Report Row 6
How do I make my report look like the above? I was originally trying to put
the PROFIT row in the header. That didn't work. Didn't work in the footer
either. When I put it in the detail section, it's repeated over and over for
each group. I'd be happy if I could get it under the last column like above
and shown "one-time" in the report.
Click to expand...

I don't understand your example, using Sum in the detail
section doesn't make sense to me. Shouldn't you be using
something more like:

AFees ... BFees ... CFees ... (AFees + BFees + CFees) ...
SellPrice ... (SellPrice - txtFees)

and the report footer:

=Sum([SellPrice]-Sum(AFees + BFees + CFees)

where AFees + BFees + CFees are fields in the report' record
source table/query. I didn't see what your ABCFees is
supposed to be, so I'm just guessing about that.
Click to expand...

OK - I'm the idiot!
I was trying to put it in the page footer. When you wrote report footer, it
caught my eye.
Now I'm kicking myself...
thanks all..!!!
 
I have basically the same problem but none of these examples help me.
The calculated field that's being summed is a function
(=Price(Afee,Bfee)) for example. When I put the total in the page
footer as =Sum(Price(Afee,Bfee)) all I get is #Error. I have tried a
lot of other things from many different forums and Microsoft and nothing
evens comes close to making this work. Is it possible or should I just
give up and type it in?
 
Can you calculate a column in your query:
PriceCalc:Price([AFee],[BFee])
Then, you can use
=Sum(PriceCalc)
 
Ok, I put the calculation in the query, and I have the result
(Pricecalc) as a field in the detail line. In the page footer, I
made a field using =Sum(Pricecalc) and I still get #Error. What am I
doing wrong?
 
Sorry I didn't notice your reference to Page Footer. This type of expression
works in Group and Report Footers, not Page footers. You would need to
create a running sum on the PriceCalc field and then reference the value of
the control in the Page Footer.
 
Back
Top