How to Total the Averages Calculated in a Group

[Dave]

How do I get Access to add the averages calculated on a report? When I try to
do a sum at the end, it just adds all the cost fields in the report together.
I used expresion builder to change the SUM field from "Cost" to "Avg of Cost"
(field generated by the report), but that didn't work. When I run the report,
it asks me for the value of "Avg of Cost".

 

[Ken Snell \(MVP\)]

How are you calculating the Average values in the report? If you're using an
expression in the Control Source of the textboxes that show the average
values, you must repeat the average expression(s) in that final summing
textbox and then use Sum on that expression.

If this isn't successful, give us some additional details about your
report's setup, the recordsource query, the fields, the Sorting & Grouping
information, and the expressions you're using.

 

[Dave]

The report is based off of one query. I tried using “=Sum(Avg([Cost]))†like
you suggested, but I get an error of “Can’t have aggregate function in
expression.†The “=Avg([Cost])†expression is entered once in the Group (by
Run) footer. I just showed it twice here for clarity. The sorting is by date
and run. The cost field is in currency format. I just want to add the
averages to get a total for the month.

Group (by Month)
Group (by Run)
Run1…3/1/8…[Cost]
Run1…3/1/8…[Cost]
……………………………=Avg([Cost])
Run2…3/1/8…[Cost]
Run2…3/1/8…[Cost]
……………………………=Avg([Cost])
(End of Group Run)
……………………………=Sum([Avg Of Cost])
(End of Group Month)

 

[Ken Snell \(MVP\)]

OK.

Add an extra textbox to the GroupFooter for the Run group. Name it
"txtSumming" and make it invisible. Set its ControlSource to "=Avg([Cost])".
Set its Running Sum value to "Over All".

Then change the ControlSource of the sum textbox in the GroupFooter for the
Month group to "=[txtSumming]".

--

Ken Snell
<MS ACCESS MVP>

The report is based off of one query. I tried using "=Sum(Avg([Cost]))"
like
you suggested, but I get an error of "Can't have aggregate function in
expression." The "=Avg([Cost])" expression is entered once in the Group
(by
Run) footer. I just showed it twice here for clarity. The sorting is by
date
and run. The cost field is in currency format. I just want to add the
averages to get a total for the month.

Group (by Month)
Group (by Run)
Run1.3/1/8.[Cost]
Run1.3/1/8.[Cost]
...........=Avg([Cost])
Run2.3/1/8.[Cost]
Run2.3/1/8.[Cost]
...........=Avg([Cost])
(End of Group Run)
...........=Sum([Avg Of Cost])
(End of Group Month)

How are you calculating the Average values in the report? If you're using
an
expression in the Control Source of the textboxes that show the average
values, you must repeat the average expression(s) in that final summing
textbox and then use Sum on that expression.

If this isn't successful, give us some additional details about your
report's setup, the recordsource query, the fields, the Sorting &
Grouping
information, and the expressions you're using.

 

[Dave]

Thank you for your help. That was the fix I needed.

-Dave

OK.

Add an extra textbox to the GroupFooter for the Run group. Name it
"txtSumming" and make it invisible. Set its ControlSource to "=Avg([Cost])".
Set its Running Sum value to "Over All".

Then change the ControlSource of the sum textbox in the GroupFooter for the
Month group to "=[txtSumming]".

--

Ken Snell
<MS ACCESS MVP>

The report is based off of one query. I tried using "=Sum(Avg([Cost]))"
like
you suggested, but I get an error of "Can't have aggregate function in
expression." The "=Avg([Cost])" expression is entered once in the Group
(by
Run) footer. I just showed it twice here for clarity. The sorting is by
date
and run. The cost field is in currency format. I just want to add the
averages to get a total for the month.

Group (by Month)
Group (by Run)
Run1.3/1/8.[Cost]
Run1.3/1/8.[Cost]
...........=Avg([Cost])
Run2.3/1/8.[Cost]
Run2.3/1/8.[Cost]
...........=Avg([Cost])
(End of Group Run)
...........=Sum([Avg Of Cost])
(End of Group Month)

How are you calculating the Average values in the report? If you're using
an
expression in the Control Source of the textboxes that show the average
values, you must repeat the average expression(s) in that final summing
textbox and then use Sum on that expression.

If this isn't successful, give us some additional details about your
report's setup, the recordsource query, the fields, the Sorting &
Grouping
information, and the expressions you're using.
--

Ken Snell
<MS ACCESS MVP>



How do I get Access to add the averages calculated on a report? When I
try
to
do a sum at the end, it just adds all the cost fields in the report
together.
I used expresion builder to change the SUM field from "Cost" to "Avg of
Cost"
(field generated by the report), but that didn't work. When I run the
report,
it asks me for the value of "Avg of Cost".

 

[KARL DEWEY]

I just want to add the averages to get a total for the month.
Check the answer it gives by doing the math the long way and I think it will
not match. You can not add averages and get a meaningful answer.

--
KARL DEWEY
Build a little - Test a little

Thank you for your help. That was the fix I needed.

-Dave

OK.

Add an extra textbox to the GroupFooter for the Run group. Name it
"txtSumming" and make it invisible. Set its ControlSource to "=Avg([Cost])".
Set its Running Sum value to "Over All".

Then change the ControlSource of the sum textbox in the GroupFooter for the
Month group to "=[txtSumming]".

--

Ken Snell
<MS ACCESS MVP>

The report is based off of one query. I tried using "=Sum(Avg([Cost]))"
like
you suggested, but I get an error of "Can't have aggregate function in
expression." The "=Avg([Cost])" expression is entered once in the Group
(by
Run) footer. I just showed it twice here for clarity. The sorting is by
date
and run. The cost field is in currency format. I just want to add the
averages to get a total for the month.

Group (by Month)
Group (by Run)
Run1.3/1/8.[Cost]
Run1.3/1/8.[Cost]
...........=Avg([Cost])
Run2.3/1/8.[Cost]
Run2.3/1/8.[Cost]
...........=Avg([Cost])
(End of Group Run)
...........=Sum([Avg Of Cost])
(End of Group Month)






:

How are you calculating the Average values in the report? If you're using
an
expression in the Control Source of the textboxes that show the average
values, you must repeat the average expression(s) in that final summing
textbox and then use Sum on that expression.

If this isn't successful, give us some additional details about your
report's setup, the recordsource query, the fields, the Sorting &
Grouping
information, and the expressions you're using.
--

Ken Snell
<MS ACCESS MVP>



How do I get Access to add the averages calculated on a report? When I
try
to
do a sum at the end, it just adds all the cost fields in the report
together.
I used expresion builder to change the SUM field from "Cost" to "Avg of
Cost"
(field generated by the report), but that didn't work. When I run the
report,
it asks me for the value of "Avg of Cost".

 

[gladys lane]

OK.

Add an extra textbox to the GroupFooter for the Run group. Name it
"txtSumming" and make it invisible. Set its ControlSource to
"=Avg([Cost])". Set its Running Sum value to "Over All".

Then change the ControlSource of the sum textbox in the GroupFooter for
the Month group to "=[txtSumming]".

--

Ken Snell
<MS ACCESS MVP>

The report is based off of one query. I tried using "=Sum(Avg([Cost]))"
like
you suggested, but I get an error of "Can't have aggregate function in
expression." The "=Avg([Cost])" expression is entered once in the Group
(by
Run) footer. I just showed it twice here for clarity. The sorting is by
date
and run. The cost field is in currency format. I just want to add the
averages to get a total for the month.

Group (by Month)
Group (by Run)
Run1.3/1/8.[Cost]
Run1.3/1/8.[Cost]
...........=Avg([Cost])
Run2.3/1/8.[Cost]
Run2.3/1/8.[Cost]
...........=Avg([Cost])
(End of Group Run)
...........=Sum([Avg Of Cost])
(End of Group Month)

How are you calculating the Average values in the report? If you're
using an
expression in the Control Source of the textboxes that show the average
values, you must repeat the average expression(s) in that final summing
textbox and then use Sum on that expression.

If this isn't successful, give us some additional details about your
report's setup, the recordsource query, the fields, the Sorting &
Grouping
information, and the expressions you're using.
--

Ken Snell
<MS ACCESS MVP>



How do I get Access to add the averages calculated on a report? When I
try
to
do a sum at the end, it just adds all the cost fields in the report
together.
I used expresion builder to change the SUM field from "Cost" to "Avg
of
Cost"
(field generated by the report), but that didn't work. When I run the
report,
it asks me for the value of "Avg of Cost".

 

[gladys lane]

The report is based off of one query. I tried using “=Sum(Avg([Cost]))â€
like
you suggested, but I get an error of “Can’t have aggregate function in
expression.†The “=Avg([Cost])†expression is entered once in the Group
(by
Run) footer. I just showed it twice here for clarity. The sorting is by
date
and run. The cost field is in currency format. I just want to add the
averages to get a total for the month.

Group (by Month)
Group (by Run)
Run1…3/1/8…[Cost]
Run1…3/1/8…[Cost]
……………………………=Avg([Cost])
Run2…3/1/8…[Cost]
Run2…3/1/8…[Cost]
……………………………=Avg([Cost])
(End of Group Run)
……………………………=Sum([Avg Of Cost])
(End of Group Month)

How are you calculating the Average values in the report? If you're using
an
expression in the Control Source of the textboxes that show the average
values, you must repeat the average expression(s) in that final summing
textbox and then use Sum on that expression.

If this isn't successful, give us some additional details about your
report's setup, the recordsource query, the fields, the Sorting &
Grouping
information, and the expressions you're using.