Use Crosstab query in subform

[TrekBiker]

I'm wanting to assemble data in several queries into a single form, including three subforms based on crosstab queries if possible, as they give a neat layout. PHV in thread 702-1347583 gave a way of doing this using modifications to the crosstabs like this, but I didn't understand.

He said

Amend your crosstab query to have fixed number of columns:
TRANSFORM ...
...
PIVOT pivotField IN (value1, value2, ...)

One of my crosstabs looks like this

PARAMETERS Forms!frmByJudge.sfmJudgeComments.Form.Company Text ( 255 ), Forms!frmByJudge.Category Text ( 255 );
TRANSFORM Sum(qryScoresCurrentCompany.[Presentation Score]) AS [SumOfPresentation Score]
SELECT qryScoresCurrentCompany.Criterion
FROM qryScoresCurrentCompany
WHERE (((qryScoresCurrentCompany.Category)=[forms]![frmByJudge].[Category]) AND ((qryScoresCurrentCompany.Company)=[Forms]![frmByJudge].[sfmJudgeComments].[form].[Company]))
GROUP BY qryScoresCurrentCompany.Criterion
PIVOT qryScoresCurrentCompany.Judge;

Any guidance would be much appreciated.

 

[dhookom]

Try set the source object property of the subform control to the crosstab query.
Query.[Your query name]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks, tried that but it doesn't list the available fields from the query for placement in the subform. I found this using the Form Wizard too, and read that a crosstab has dynamic columns, or something like that. Hence the PHV thread about doing a transformation, but I didn't understand in detail what he meant.

 

[dhookom]

In my suggested solution, there is no subform. There is an empty subform control with just the crosstab query.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Ah, sorry. Tried this instead ..'

... but got error #Name?

 

[dhookom]

This works for me with a crosstab. The control type is Subform/Subreport and the Source Object property is Query.MyQueryName.

Is your form frmByJudge open?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Yes, it's open. Will have to return to this in a few hours, bike ride calls. Thanks.

 

[dhookom]

I'm hoping for a ride later today. Strava?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Just Garmin Connect for 90 min local ride in the sunshine. Enjoy yours.

Can't replicate your example for some reason. This is the crosstab query

This is design view

But get this error message

 

[dhookom]

Can you create and test a very simple example?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016