Re: How to Total the Averages Calculated in a Group

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


"Dave" wrote:

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

-Dave



"Ken Snell (MVP)" wrote:

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>


"Dave" <Dave@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:19FFCC6B-41C3-48E2-B588-8C589E085737@xxxxxxxxxxxxxxxx
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)" wrote:

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>



"Dave" <Dave@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:08F67846-2798-4B22-BD11-8D99B99722B2@xxxxxxxxxxxxxxxx
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".






.

Relevant Pages

  • Re: How to Total the Averages Calculated in a Group
    ... "Ken Snell (MVP)" wrote: ... Add an extra textbox to the GroupFooter for the Run group. ... Set its Running Sum value to "Over All". ...
    (microsoft.public.access.reports)
  • Re: A Challenge Query
    ... "Ken Snell (MVP)" wrote: ... suggested query design here, but this may get you started: ... I need to Take The beginning Balance and subtract only the Sum of Hours ...
    (microsoft.public.access.queries)
  • Re: Import/Export Multiple Excel Files to Access
    ... "Ken Snell (MVP)" wrote: ... Dim strPathFile As String, strFile As String, strPath As String ... Create a new macro, and name it ...
    (microsoft.public.access.macros)
  • Re: Import from Excell Macro
    ... strDirectory = Left(strInputFileName, _ ... "Ken Snell (MVP)" wrote: ... Dim strFileName As String, strInputFileName As String ...
    (microsoft.public.access.macros)
  • Re: Combo Box text display problem based on AfterUpdate...
    ... "Ken Snell (MVP)" wrote: ... You need to make these changes so that the query will work. ... INNER JOIN tblManager AS M ...
    (microsoft.public.access.forms)