Average in a query after a number of rows

  • Thread starter Thread starter Audrey1980
  • Start date Start date
A

Audrey1980

Hi,

I am trying to get the average of a set of values after a number of rows.
What I need is the average margin across a set of departments and
subdepartments. (Average grouped at subdepartment level)
Here is the query I currently have:

PARAMETERS [Forms]![Main Menu]![RSM] Text ( 255 ), [Forms]![Main
Menu]![Week] Short, [Forms]![Main Menu]![Year] Short;
TRANSFORM
IIf(Sum([VALUEAMOUNTEXCVAT])=0,0,Sum([MARGIN])/Sum([VALUEAMOUNTEXCVAT])) AS
[margin %]
SELECT [MSR HIERARCHY].ValueType, VALIDDATABODY.MSRDepartment,
VALIDDATABODY.MSRDepartmentName, VALIDDATABODY.MSRSubDepartment,
VALIDDATABODY.MSRSubDepartmentName
FROM (VALIDDATABODY LEFT JOIN [RSM STORE LISTING] ON
VALIDDATABODY.storeNumber = [RSM STORE LISTING].Store) LEFT JOIN [MSR
HIERARCHY] ON (VALIDDATABODY.MSRSubDepartment = [MSR
HIERARCHY].MSRSubDepartment) AND (VALIDDATABODY.MSRDepartment = [MSR
HIERARCHY].MSRDepartment)
WHERE ((([RSM STORE LISTING].RSM)=[Forms]![Main Menu]![RSM]) AND
((VALIDDATABODY.week)=[Forms]![Main Menu]![Week]) AND
((VALIDDATABODY.year)=[Forms]![Main Menu]![Year]))
GROUP BY [MSR HIERARCHY].ValueType, VALIDDATABODY.MSRDepartment,
VALIDDATABODY.MSRDepartmentName, VALIDDATABODY.MSRSubDepartment,
VALIDDATABODY.MSRSubDepartmentName
ORDER BY VALIDDATABODY.MSRDepartment, VALIDDATABODY.MSRSubDepartment
PIVOT [RSM STORE LISTING].[Store Name];


If you can tell me how to do it in design view - even better
Thanks
 
I am trying to get the average of a set of values after a number of rows.
What field do you want to average?
What number of 'rows' and counting from where?
 
Back
Top