parameters and crosstab queries

[grantschneider]

I have the following crosstab query:

TRANSFORM Nz(Sum(MSF07.[Share Total]),0) AS [SumOfShare Total]
SELECT MSF07.Aircraft, Sum(MSF07.[Share Total]) AS [Total Of Share
Total]
FROM MSF07
GROUP BY MSF07.Aircraft
PIVOT MSF07.Account;

I want to be able to set a parameter to choose a month. Everytime I
try to do this it gives me errors since the [month] column isnt part
of the query.

Thanks
Grant

 

[Allen Browne]

You can use a parameter in a crosstab, as long as you declare it.

Go to Parameters on the Query menu, and enter the parameter there.

Don't use the name Month though. That's a reserved word in JET:
http://allenbrowne.com/AppIssueBadWord.html

More crosstab query techniques:
http://allenbrowne.com/ser-67.html

 

[John Spencer]

One, you will have to declare the parameter (Select Query: Parameters from
the menu)

Two, you will have to add the field you want to apply the criteria against
to the query. Set its total to WHERE and then set the criteria to the
parameter name.

You would end up with something likethe following depending on data type of
your month field.

Parameters [What Month] Text (255);
TRANSFORM Nz(Sum(MSF07.[Share Total]),0) AS [SumOfShare Total]
SELECT MSF07.Aircraft
, Sum(MSF07.[Share Total]) AS [Total Of Share Total]
FROM MSF07
WHERE SomeFieldWithMonthName = [What Month]
GROUP BY MSF07.Aircraft
PIVOT MSF07.Account;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..