Crosstab queries and parameters that are not working

[mmasuda]

I have a table “caller” which displays among other info the type of caller, the quarter of the fiscal year, and the year.
I want to run a crosstab query which displays the count of the type of caller and prompt the user to enter which fiscal year quarter and year should be queried.
The crosstab query runs fine and displays all the information. I cannot, however, convince it to ask the user for the quarter and the year. In design view, I tried to use the query > parameter option. It does ask to input the quarter and the year but still displays all the data. Any ideas?

 

[PHV]

Have you declared the parameters with the correct type ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mmasuda]

I hope so. In the table I used text for both year and quarter, so I used the same for the parameter.

 

[PHV]

Can you post the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mmasuda]

Yes. This is without parameters.

TRANSFORM Count(caller.caller_id) AS totalIntakesOfQuarter
SELECT caller.caller_type, caller.Quarter
FROM caller
GROUP BY caller.caller_type, caller.Quarter
PIVOT caller.Year;

When I use parameters it looks like this

PARAMETERS [Enter Quarter] Text ( 255 ), [Enter Year] Text ( 255 );
TRANSFORM Count(caller.caller_id) AS totalIntakesOfQuarter
SELECT caller.caller_type, caller.Quarter
FROM caller
GROUP BY caller.caller_type, caller.Quarter
PIVOT caller.Year;

thanks for helping.

 

[PHV]

Without a WHERE clause, it's standard behaviour to get all the records ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mmasuda]

Adding the "where" worked perfectly.
Thank you very much.