Average of Totals

[Ferdie]

I have a Subtotal (SUM) for the Month of all the balances of several
accounts...I need to Average the Subtotal (Sum) for each month. When I use
the average function, it averages the entire population, which is NOT what I
want.

Any help?

 

[Lynn Trapp]

Can you post the SQL for your query?

 

[John W. Vinson]

I have a Subtotal (SUM) for the Month of all the balances of several
accounts...I need to Average the Subtotal (Sum) for each month. When I use
the average function, it averages the entire population, which is NOT what I
want.

Any help?

You'll probably need either a Totals query (to calculate the averages) based
on another Totals query (calculating the subtotal); or you can use the Sorting
and Grouping feature of a Report to do the same thing. In any case you'll need
two levels of summation.

If you would like help doing so please post the SQL view of your current
query, and indicate what you want averaged, and over what groups.

 

[Ferdie]

Hi John,
Thanks for you quick reply. I am actually trying to do this in the Reports.
Lets say, in the details, there are different account number with
corresponding balances. These balances are totalled in the footer of that
Group (month) =Sum([balances]). Then, I need to Average all the monthly
totals in the footer of the Report. In the report footer, if i use
=Avg([balances]) , it averages ALL the details, NOT the monthly totals.
Hope this clarifies the problem. thanks.

 

[Ferdie]

Hi Lynn,

Hi John,
Thanks for you quick reply. I am actually trying to do this in the Reports.
Lets say, in the details, there are different account number with
corresponding balances. These balances are totalled in the footer of that
Group (month) =Sum([balances]). Then, I need to Average all the monthly
totals in the footer of the Report. In the report footer, if i use
=Avg([balances]) , it averages ALL the details, NOT the monthly totals.
Hope this clarifies the problem. thanks.

 

[Ferdie]

I am actually trying to do this in the Reports. Lets say, in the details,
there are different account number with corresponding balances. These
balances are totalled in the footer of that Group (month) =Sum([balances]).
Then, I need to Average all the monthly totals in the footer of the Report.
In the report footer, if i use =Avg([balances]) , it averages ALL the
details, NOT the monthly totals.
Hope this clarifies the problem. thanks.

 

[John Spencer]

I think this will work if I haven't gotten my math facts messed up.

Add another control to your Group Footer:
Name: txtMonthCount
Control Source: =1
Running Sum: Over All

In the report footer:
Add a control
Name: MyAvg
Control Source: Sum([Balances])/[txtMonthCount]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I am actually trying to do this in the Reports. Lets say, in the details,
there are different account number with corresponding balances. These
balances are totalled in the footer of that Group (month) =Sum([balances]).
Then, I need to Average all the monthly totals in the footer of the Report.
In the report footer, if i use =Avg([balances]) , it averages ALL the
details, NOT the monthly totals.
Hope this clarifies the problem. thanks.

I have a Subtotal (SUM) for the Month of all the balances of several
accounts...I need to Average the Subtotal (Sum) for each month. When I use
the average function, it averages the entire population, which is NOT what I
want.

Any help?