crosstab query parameter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jer - 01 Aug 2005 23:46 GMT

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;

Signature

thanks as always for the help
jer

Reply to this Message

jer - 02 Aug 2005 00:10 GMT

Figured it out thanks

Signature

thanks as always for the help

> 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
[quoted text clipped - 8 lines]
> GROUP BY q3.Year, q3.Teacher, q3.Period
> PIVOT q3.Day;

Reply to this Message

John Spencer (MVP) - 02 Aug 2005 00:19 GMT

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
[quoted text clipped - 12 lines]
> thanks as always for the help
> jer

Reply to this Message

The Mecca - 09 Aug 2005 16:59 GMT

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:
[quoted text clipped - 23 lines]
> > thanks as always for the help
> > jer

Reply to this Message