crosstab query parameter

[Guest]

Help. I am trying to prompt a user to input criteria in a cross tab query.
In a normal select query this would be accomplished by [enter name] in the
criteria. I have searched the newsgroup and found that you have to input the
prompt under Query - Parameters. I have done this and still the query is
returning all records. Am I missing something. Following is the query

PARAMETERS [Enter Teacher Name] Text;
TRANSFORM First(q3.TTSubject) AS FirstOfTTSubject
SELECT q3.Year, q3.Teacher, q3.Period
FROM q3
GROUP BY q3.Year, q3.Teacher, q3.Period
PIVOT q3.Day;

 

[Guest]

Figured it out thanks

 

[John Spencer (MVP)]

YOu've defined the parameter, but you've never used it in the query in either a
WHERE Clause or a HAVING Clause. I think what you want is:

PARAMETERS [Enter Teacher Name] Text;
TRANSFORM First(q3.TTSubject) AS FirstOfTTSubject
SELECT q3.Year, q3.Teacher, q3.Period
FROM q3
WHERE Q3.Teacher = [Enter Teacher Name]
GROUP BY q3.Year, q3.Teacher, q3.Period
PIVOT q3.Day;

 

[Guest]

I thought this could answer my questions on crosstab parameters- but it seems
I have a little different scenario.

I want my query to prompt for a month- they will chose a number between 1
and 12. that will then correspond with the January - December (which I have
in a seperate table) I want that value to be not only the filter for the
query results, but also my column header. Is that possible....

For example:
if the user were to want to see January's totals, it would look like:

OPRID Agent_Name Sup_Name Total January-1 January-2
AKL Anna Lentz Angela Watts 2 1
1

The number after the Month is the week number. so I have a table that has
all the details, including a number for the month (ex. jan=1) and a number
for the week (ex. week=1). the month and the week are in seperate colums.

Then a table with the corresponding month words- (ex. january=1, february=2)

The crosstab query is based upon a query that already prompts for the month
number.

I know that is a lot of information- I hope someone can make sense of it all!

YOu've defined the parameter, but you've never used it in the query in either a
WHERE Clause or a HAVING Clause. I think what you want is:

PARAMETERS [Enter Teacher Name] Text;
TRANSFORM First(q3.TTSubject) AS FirstOfTTSubject
SELECT q3.Year, q3.Teacher, q3.Period
FROM q3
WHERE Q3.Teacher = [Enter Teacher Name]
GROUP BY q3.Year, q3.Teacher, q3.Period
PIVOT q3.Day;

Help. I am trying to prompt a user to input criteria in a cross tab query.
In a normal select query this would be accomplished by [enter name] in the
criteria. I have searched the newsgroup and found that you have to input the
prompt under Query - Parameters. I have done this and still the query is
returning all records. Am I missing something. Following is the query

PARAMETERS [Enter Teacher Name] Text;
TRANSFORM First(q3.TTSubject) AS FirstOfTTSubject
SELECT q3.Year, q3.Teacher, q3.Period
FROM q3
GROUP BY q3.Year, q3.Teacher, q3.Period
PIVOT q3.Day;