Trying to limit crosstab with "where" statement using form field parameter

[Steve_b]

I am attempting to develop a cross tab query that limits the years to a parameter that is stored in a form. Currently, the query creates a column for every year in the table. I am not quite sure how to proceed as the query does not recognize the form reference. Thanks for a push in the right direction. Here is the SQL

TRANSFORM Nz(Sum(TeacherResources.TeacherFTE),0) AS SumOfTeacherFTE
SELECT TeacherResources.trschool_id, Sum(TeacherResources.TeacherFTE) AS [Total Of TeacherFTE]
FROM TeacherResources
GROUP BY TeacherResources.trschool_id
PIVOT TeacherResources.tryear;

 

[pbaldy]

Re: Trying to limit crosstab with "where" statement using form field parameter

Note the bit on parameters:

http://allenbrowne.com/ser-67.html

 

[isladogs]

Re: Trying to limit crosstab with "where" statement using form field parameter

Alternatively do one of the following:

1. Create a standard query that is filtered on your form value
Then base your crosstab query on that instead

2. Use a user defined function to grab the value required from your form
See attached screenshot

 

[Steve_b]

Re: Trying to limit crosstab with "where" statement using form field parameter

Thank you both. The Browne article was helpful. I have it working.

 

[pbaldy]

Re: Trying to limit crosstab with "where" statement using form field parameter

No problem.