A
Audrey1980
Hi
I have a query with 3 tables - RSM Store listing (holding employee data),
MSR Hierarchy (department listings) , validdatabody. Validdatabody includes
margin values against week and year for the store and sales person (employee
aka RSM).
I am trying to create a crosstab query to get the margin(value) for a
specific department and subdepartment (row headings) for each store (column
heading). I am taking the values specified in a form to show data only for a
specific employee, week and year. Currently I am doing this in design view -
I am using the expression builder in the where clause to try to pull data
from the form where the user specifies the RSM, Week and Year. However, it
isnt recognising the form field names. I am getting an error stating that
Access does not recognise ‘[Forms]![Main Menu]![RSM]’ as a valid field name
or expression. Please help as I've drilled down to get this using the
Expression Builder.
Here is the corresponding SQL statement. Any ideas??
Thanks
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.MSRDepartment = [MSR HIERARCHY].MSRDepartment)
AND (VALIDDATABODY.MSRSubDepartment = [MSR HIERARCHY].MSRSubDepartment)
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 [MSR HIERARCHY].ValueType, VALIDDATABODY.MSRDepartment
PIVOT [RSM STORE LISTING].[Store Name]
I have a query with 3 tables - RSM Store listing (holding employee data),
MSR Hierarchy (department listings) , validdatabody. Validdatabody includes
margin values against week and year for the store and sales person (employee
aka RSM).
I am trying to create a crosstab query to get the margin(value) for a
specific department and subdepartment (row headings) for each store (column
heading). I am taking the values specified in a form to show data only for a
specific employee, week and year. Currently I am doing this in design view -
I am using the expression builder in the where clause to try to pull data
from the form where the user specifies the RSM, Week and Year. However, it
isnt recognising the form field names. I am getting an error stating that
Access does not recognise ‘[Forms]![Main Menu]![RSM]’ as a valid field name
or expression. Please help as I've drilled down to get this using the
Expression Builder.
Here is the corresponding SQL statement. Any ideas??
Thanks
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.MSRDepartment = [MSR HIERARCHY].MSRDepartment)
AND (VALIDDATABODY.MSRSubDepartment = [MSR HIERARCHY].MSRSubDepartment)
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 [MSR HIERARCHY].ValueType, VALIDDATABODY.MSRDepartment
PIVOT [RSM STORE LISTING].[Store Name]