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?

Can you post the SQL for your query?

--
Lynn Trapp


"Ferdie" wrote:

> 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?

On Wed, 6 Jan 2010 10:34:03 -0800, Ferdie <(E-Mail Removed)>
wrote:

>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.
--

John W. Vinson [MVP]

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.




"John W. Vinson" wrote:

> On Wed, 6 Jan 2010 10:34:03 -0800, Ferdie <(E-Mail Removed)>
> wrote:
>
> >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.
> --
>
> John W. Vinson [MVP]
> .
>

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.





"Lynn Trapp" wrote:

> Can you post the SQL for your query?
>
> --
> Lynn Trapp
>
>
> "Ferdie" wrote:
>
> > 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?

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" wrote:

> 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?

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

Ferdie wrote:
> 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" wrote:
>
>> 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?