Query output

[verstapp]

I have a query which takes a user-supplied parameter and produces an output like this
Date Count parameter x
=== ============

SELECT [asset tests].[test date], count(assets.[asset key])
FROM [asset tests] LEFT JOIN assets ON ([asset tests].[asset id] = assets.[asset key]) and (assets.[test period] =[Enter test period] or assets.[visual period] =[Enter test period])
GROUP BY [test date];

I currently run the query 3 times with the 3 possible parameters and then copy-and-paste the results to produce an output like that below.

I would like to query all 3 parameters simultaneously, producing
Date Count parameter1 Count parameter2 Count parameter3
=== =========== =========== ==========

Is it possible to do this in one query?

 

[dhookom]

I believe you could do this with a crosstab query. I would allow the user to enter values into a form and then run the crosstab.

Another solution is to create a small [Period] table with three records. Add this to the above query and substitute [Enter Test Period] with your [Period] field. Then convert to a crosstab that has Test Date as the Row Heading, [Period] as the Column Heading, and Count(Asset Key] as the value.

Duane
Hook'D on Access
MS Access MVP