getting values from a form

  • Thread starter Thread starter Audrey1980
  • Start date Start date
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]
 
Insert this line before TRANSFORM --
PARAMETERS [Forms]![Main Menu]![RSM] Text ( 255 ), [Forms]![Main
Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );
 
Just the ticket. Thank you

KARL DEWEY said:
Insert this line before TRANSFORM --
PARAMETERS [Forms]![Main Menu]![RSM] Text ( 255 ), [Forms]![Main
Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );

Audrey1980 said:
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]
Click to expand...
Click to expand...
 
Back
Top