Total over all

[BHScripter]

Hi:
I have a report which is grouped on Location and it lists a total per month of services and then an average of those totals per location:

ex:
Location 1
Jan 23.75
Feb 25.00
Average: 24.38
Calculation for text box:=Avg([SumOfTotal Month])

Location 2
Ditto

I need to do a total of these averages in the report footer and I'm having a heck of a time getting that to work.

Any help would be greatly appreciated,
Brianna

 

[LarryDeLaruelle]

Brianna:

Just copy the text box/expressions in the group footer band and paste them to rhe report footer band. In the report footer, the expressions operate on the entire record set.

Make sure it is the report footer; the calculations will not work in the page footer.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[cghoga]

I know this is going to sound weird, but try this.

In your detail section where you're calculating Avg, include another textbox. Set it equal to the Avg field. For example, if the avg field is named 'Average' make the new field's Control Source = [Average]. Set the running total property to Over All. Set the Invisible property of the field = Yes.

Now, within the footer, in the field you're using to view the total, set it's Control Source to the invisible field.
For example, Control Source = [InvisibleFieldName].

Now, run your report.

Does this work?

 

[BHScripter]

Hi Cghoga:

I tried that and when I run the report it asks me for the parameter value of that field I'm referencing???

I copied the field name directly from the name field of that calculated text box.

What am I missing?

 

[cghoga]

I'm sorry Brianna.

Try what Larry recommended.

I don't want to lead you down the wrong track.

If Larry's recommendation doesn't work for you please let us know and we can go from there.

 

[BHScripter]

Hi Larry
i copied and pasted the function which computed the average per group into the report footer and it gave me the average for all the records (i.e. the total/the number of records) rather than a total of the average amounts.

???

 

[LarryDeLaruelle]

Brianna:

Sorry, I thought that was what you wanted.

If you want a total of the averages, you may need to do a little vba coding to accumulate that result.

Let me play with this to see if I can find a simpler solution. Given time, I should be able to get something for you tomorrow.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[LarryDeLaruelle]

Brianna:

cghoga had you going on the right track.

Here is how I solved it with my control names (change to yours as appropriate):

In the group footer I have two unbound text controls set as follows:

1. Name: txtAverage1 with the expression =Avg([Amount1])
2. Name: txtSum1 with the expression =[txtAverage1] and the Running Sum set to Over All and Visible set to No. (Actually, I tried it with both Over All and Over Group and got the same result from each.)

In the report footer I have one unbound text control set as follows:

1. Name: txtTotal1 with the expression =[txtSum1]

In my tests, this worked just fine to accumulate the total of the averages displayed in the group footer.

Let me know if this does it for you.


Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[BHScripter]

Thanks so much - I will try it right now

 

[BHScripter]

It worked - thanks so much!!!!!