Ennistymon
asked on
Crosstab Query Based on a Parameter Query
I have a parameter query that I am using as the source of a crosstab query. The parameter is based on the date field and the date field drives the columns in the crosstab query. I know the way to do this is to define the columns in the properties for the crosstab query. My question is how to do define the columns when it could be any date range the user selects? Is there a way to assign a variable to the column fields?
GRayL
Can you post the SQL's for both the Parameter Query and the Cross Tab query?
I've just run a simple testing using a Parameter query as the basis for a Cross tab - no problem.
ASKER
The problem comes whn you try to pass the crosstab query to the report wizard. It comes back with no records and will not allow a report. As I understand it you then need to define the columns in the crosstab query . When you do that, the detail does not come back only the totals. SQL to follow:
Parameter Query
SELECT tblExecptions.Date, tblExecptions.Comments, tblExecptions.Type, tblExecptions.[Name / SCAC]
FROM tblExecptions
WHERE (((tblExecptions.Date) Between [Start date] And [End date]) AND ((tblExecptions.Type)="Mis
Crosstab Query
PARAMETERS [Start date] DateTime, [End date] DateTime;
TRANSFORM Count([tblExecptions Query].Comments) AS CountOfComments
SELECT [tblExecptions Query].[Name / SCAC], Count([tblExecptions Query].Comments) AS [Total Of Comments]
FROM [tblExecptions Query]
GROUP BY [tblExecptions Query].[Name / SCAC]
PIVOT Format([Date],"Short Date") In ("1","2","3","4","5","6","
Parameter Query
SELECT tblExecptions.Date, tblExecptions.Comments, tblExecptions.Type, tblExecptions.[Name / SCAC]
FROM tblExecptions
WHERE (((tblExecptions.Date) Between [Start date] And [End date]) AND ((tblExecptions.Type)="Mis
Crosstab Query
PARAMETERS [Start date] DateTime, [End date] DateTime;
TRANSFORM Count([tblExecptions Query].Comments) AS CountOfComments
SELECT [tblExecptions Query].[Name / SCAC], Count([tblExecptions Query].Comments) AS [Total Of Comments]
FROM [tblExecptions Query]
GROUP BY [tblExecptions Query].[Name / SCAC]
PIVOT Format([Date],"Short Date") In ("1","2","3","4","5","6","
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
In your reference to the SELECT and TRANSFORM clauses, would putting the date in the SELECT and the SCAC in the TRANSFORM cause Date to show as a row and SCAC to show as a column?
ASKER
Thank you VERY much! You have solved my problem. Your instructions to change the SQL to the following did it, "PIVOT Day(Format([Date],"ShortDa
ASKER
Thanks!
Thanks, glad to help