If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Avereging the Subtotals in Reports
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. |
#2
|
|||
|
|||
Avereging the Subtotals in Reports
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. -- Duane Hookom Microsoft Access MVP "Ferdie" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|