Totaling Group Averages

[1starr]

I have a problem where I need to total up the sum of each group average at the end of my report. In my report for each customer I had to find out what was their average cost per month but at the end of the report I have to total up all the averages together. I have tried using Sum but that gave me the total for that column or an error message when I tried to use the txt box name. Please help.
Thank you,

 

[scriverb]

To sum up the averages of all groups you must sum up the individual costs and divide by the number of individuals. So, in the same fashion that you Summed up on the Total Costs per customer in a Report Group footer you have to perform that same function at the Report Footer. Then use that figure along with the total number of customers to execute the calculation to come up with the Average Cost per customer.

Get back with me if you still have problems.
Bob Scriver

 

[1starr]

I have average out the cost per customer in the group footer, what I need is to add up those totals in the report footer not get the average for all the customers.

 

[scriverb]

You can't Sum a field in a Footer that already is a calculated field in another section of the report. So, you said that you have calculated Average cost per customer in the group footer. That is a calculated control. You cannot not refer to that control in a subsequent footer and perform a calculation using that control. You have to use a control with data from your query or table.

Now you can however create these values in the query and then just display them in the report Detail or Footer sections. Then you can use that control for your Report Footer.

To address you specific problem the two values that you need to rollup to calculate the summed average of all customers is the cost associated with each customer and the number of customers. If you have controls with these values in them you can now use them to to calculate your Summed Avg. Costs in your Report Footer.

I hope this helps you understand the problem you are having. With a little more specifics of data at the Detail, Group Footer sections i can help with this calculation.
Bob Scriver

 

[MichaelRed]

Actually, what 1starr is asking for is not that uncommon, it is (I think) referd to as an un-weighted average, whereas the process discussed by scriverb is often refered to as a (Oh! Suprise!!!) Weighted average.

The difference between the values can be substantial, and the programmer should take care to fully understand what is necessary / desired / required.

I would hesitate to advise the use of either to 1starr (or anyone asking the question in the manner of the original post) without a careful explination (and possibly some examples) along with some dialog which indicated the receipient could make the knowledgable decision on what was appropiate.

P.S. a small module in the report could easily 'accumulate' the individual aggregates as they occur in the group footer, as well as count them - then the report footer can easily display the sum / #. But that -of course- needs to be decided by the user.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[CosmoKramer]

1starr - If you are still looking into doing this, here is a quick way to accomplish it:

Add a text box to your group footer, make it invisible, set its control source to be the text box that calculates the average, and set the Running Sum property to Over All. At the end of the report this field will contain the sum of all of the averages.

Set the control source of the text box in your report footer to be the invisible text box you just added to your group footer and you should be all set......

Hope this helps.