Avereging the Subtotals in Reports

  • Thread starter Thread starter Ferdie
  • Start date Start date
F

Ferdie

I am trying to do this in the Reports.
Lets say, in the details, there are different account numbers 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.
Any help? thanks.
 
I would create a totals query that can be added to the report's record source
query. For instance, if you want to find the Average of the Monthly Freight
values from the Orders table in Northwind, the SQL would be:

SELECT Avg(MthlyFreightAvg) as TheAvg
FROM
(SELECT Format([OrderDate],"yyyymm") AS YrMth, Avg(Orders.Freight) AS
MthlyFreightAvg
FROM Orders
GROUP BY Format([OrderDate],"yyyymm")) Q;

You can then save and add this query to your report's record source.
 
Back
Top