totals

[iainrkemp14]

Hi all,

I am currently having some trouble trying to total something.

I have 5 'text boxes' in a Project_Name Header. One of these text boxes is called 'Label39'.

In my report footer I want a text box that displays the sum off all the 'Label39' instances.

I have tried =Sum([Label39]) but when I try to run the report it prompts me for the 'Label39' paramter.

Is there any way I can display a sum of every instance of this text box in the report footer?

Cheers,

- Iain

 

[PaulBricker]

It sounds like Label39 is a calculated value. If it is, then you have to do the entire calculation again in the footer.
For example, if the Control Source for Label39 is
=Txt1 + Txt2 + Txt3
Then your footer would have to be
=Sum(Txt1 + Txt2 + Txt3)

Paul

 

[iainrkemp14]

Thanks for responding! I have tried to do that but never seem to get the syntax quite right, perhaps you could help?

Label39 is a calculated value: =[Hourly Rate]*[Label48]
Label48 is a calculated value: =Sum([SumOfHours])


I tried the following without success:

=Sum([Hourly Rate]*[Sum(SumOfHours)])

And it prompts me for Sum(SumOfHorus)!

Any thoughts?

 

[PaulBricker]

Well, it seems that you have already broken the rule and gotten away with it when you calculate Label39 but that may be because they are both in the Header. Try this
=Sum([HourlyRate]) * Sum([SumOfHours])


Paul

 

[dhookom]

Try:
=Sum([Hourly Rate]*[SumOfHours])


Duane
MS Access MVP

 

[iainrkemp14]

Thanks,

I've tried this and doesn't quite work. The problem is the 'Hourly Rate' that is referred to varies slightly between records (some our 54, some 30, etc). In the Project_Name_Header it matches the related 'Hourly Rate' and 'SumOfHours' records. When I put 'Hourly Rate' in the report footer however it takes the last 'Hourly Rate Value' so the calucation is not an accurate sum.

What a pain in the $^&(!

- Iain

 

[dhookom]

If you use
=Sum([Hourly Rate]*[SumOfHours])
the result will be the same as stepping through each record in your report and multiplying the [Hourly Rate] field times the [SumOfHours] field. The Hourly Rate will be applied on a record by record basis. The Sum of all these individual calculations will display in the text box.

If you use
=[Hourly Rate]*Sum([SumOfHours])
the SumOfHours will be summed and then multiplied times the final [Hourly Rate].


Duane
MS Access MVP

 

[PaulBricker]

I ran a quick sample report with data similar to yours and
=Sum([HourlyRate] * [SumOfHours]) gave me a correct value. The assumption here is that SumOfHours is not calculated in the Report, but is a field calculated in the underlying query. If that's not correct, let us know.

Paul

 

[dhookom]

You are correct. From an earlier posting, I assumed the SumOfHours was a field in the report's record source. If it wasn't, you might be forced to use a calculated text box in the detail section with a running sum. I hate when that happens.

Duane
MS Access MVP