Grand Total Sum Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Wondering if someone could help. I have this report that has a column with the following expression

=97107.51/[Text50]*[Sum of Total Cost Allowed

Which calculates fine. However when I try to do a Grand Total Sum in the Report Footer it only returns the value of the last line of the report or nothing (depending on how I switch it up). I have tried

=Sum(97107.51/[Text50]*[Sum of Total Cost Allowed]) - Doesn't recognize most of the controls (ie. Text 50 & Sum of Total Cost Allowed) Returns a blank
=Sum(97107.51/[Text50])*([Sum of Total Cost Allowed]) - Same as above with the exception that it does recognize sum of Total Cost Allowed but not Text 50
=97107.51/[Text50]*[Sum of Total Cost Allowed] - Grabs the total of the last line of the column rather than totaling all of them

and probably several other combinations. This is driving me crazy! Can anyone help?
 
AMC,

I can see nothing wrong with
=Sum(97107.51/[Text50]*[Sum of Total Cost Allowed])
What exactly do you mean when you say it doesn't "recognize" the field?

How about you do your calculation in the query that the report is based
on, instead of in the report itself? I.e. make a calculated field in
the query, like this...
MyCalc: 97107.51/[Text50]*[Sum of Total Cost Allowed]
.... and then in your report footer unbound control, you then just need
=Sum([MyCalc])
 
AMC said:
Wondering if someone could help. I have this report that has a column with the following expression:

=97107.51/[Text50]*[Sum of Total Cost Allowed]

Which calculates fine. However when I try to do a Grand Total Sum in the Report Footer it only returns the value of the last line of the report or nothing (depending on how I switch it up). I have tried:

=Sum(97107.51/[Text50]*[Sum of Total Cost Allowed]) - Doesn't recognize most of the controls (ie. Text 50 & Sum of Total Cost Allowed) Returns a blank.
=Sum(97107.51/[Text50])*([Sum of Total Cost Allowed]) - Same as above with the exception that it does recognize sum of Total Cost Allowed but not Text 50.
=97107.51/[Text50]*[Sum of Total Cost Allowed] - Grabs the total of the last line of the column rather than totaling all of them.
Click to expand...


The aggregate functions only work with fields in the record
source table/query, they are not aware of controls in the
report.

If Text50 (horrible name) is a simple bound text box, then
the Sum should use the field that its bound to.

I find it hard to believe that it recognized Sum of Total
Cost Allowed without the square brackets, names with spaces
or other funky characters ***must*** be enclosed in [ ] for
them to be properly understood. It's a common practice to
drop the spaces and use SumOfTotalCostAllowed instead.

Anyway, if that text box is bound to a record source field,
use the field name instead of the control name.

If either of those two text boxes have a control source
expression instead of being bound to a field, then you'll
have to provide all the details of the expressions and the
source of any names they use, before I can determine the
best approach to calculating the total you're looking for.
 
Well after much trial and error there is a solution to this problem! In case anyone additional comes across a problem like this here is how I actually got this report to work.

The original expression I had in Report Footer for Grand Total was:

=([Salary Costs]/[Total Costs])*[Sum of Total Cost Allowed]
This expression was taking dividing two calculated control boxes and then multiplying it by another line in a column that was summing information. This worked well in the detail footer but was returning no value or some weird value in the report footer.

The new expression in the Report Footer to Grand Total Column information:

=([Salary Costs]/[Total Costs])*[Grand Total Total Cost Allowed]
This expression is pretty much the same with the acception that I changed the [Sum of Total Cost Allowed] to the [Grand Total Total Cost Allowed] calculated control box. Same column but just used the Grand Total from that column rather than using the detail.

:)AMC
 
Back
Top