This is the source query for what 1

[TrekBiker]

This is the source query for what I'd like to be a new subform where a main form that provides Category and Company as link fields.

I'd ideally like to display the subform data something like this.

How best to get near to achieving this?

 

[TrekBiker]

Sorry, clicked Submit before adding a sensible title!

 

[dhookom]

I would create a crosstab query and set it as the source object of a subform control. If you supplied your records in some format that could be copied and pasted, someone might be more prone to provide a SQL statement.

There is an FAQ faq701-4524 that might help.

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

 

[TrekBiker]

This was a great help, Duane, in pointing me in the right direction. Amazed to see you created it 20 years ago.

I'm assembling an equivalent for my form and data.

Many thanks.

 

[dhookom]

Most of the functionality I use in Access has been around for 20+ years. Don't ask me about the new stuff.

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

 

[TrekBiker]

Sorry to say I'm really not making any progress with converting your example to my situation, Duane.

I've simplified the starting table, like this.

Then have tblXtabColumns with FldName = Judge or Total.

The end point needs to show results for the main form's current Category, and the Company selected in its subform sfmWrittenComments.

I've been trying things like this.

TRANSFORM Sum(IIf([FldName]="Judge",[Judge], [Total])) AS DaVal
SELECT Scores.Criterion
FROM tblXtabColumns, Scores
WHERE [Category]=Forms!frmEntries.Category AND [Company]=Forms!frmEntries.sfmWrittenComments.Form.Company
GROUP BY Scores.Criterion
PIVOT [FldName] & " - " &[Judge];

This gives an error like this

 

[dhookom]

You must declare the data type of the parameters in crosstabs. Find parameters in the query properties.

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

 

[TrekBiker]

Returning to this after a break.

Using Make Table I created a source ScoresCurrentCompany for one combination of Category and Company. The Crosstabb query is

TRANSFORM Sum(ScoresCurrentCompany.[Written Score]) AS [SumOfWritten Score]
SELECT ScoresCurrentCompany.[Criterion]
FROM ScoresCurrentCompany
GROUP BY ScoresCurrentCompany.[Criterion]
PIVOT ScoresCurrentCompany.Judge;

It correctly produces this

The stumbling block is now picking up Category and Company from the main form. Tried these as criteria after defining Parameters for them as Text.

forms!frmEntries.Category
forms!frmEntries.sfmWrittenComments.form.Company

Gives this error, whether added to the source query or the crosstab query.

The new SQL was

TRANSFORM Sum(ScoresCurrentCompany.[Written Score]) AS [SumOfWritten Score]
SELECT ScoresCurrentCompany.Criterion
FROM ScoresCurrentCompany
WHERE (((ScoresCurrentCompany.Category)=[forms]![frmEntries].[Category]))
GROUP BY ScoresCurrentCompany.Criterion, ScoresCurrentCompany.Category
PIVOT ScoresCurrentCompany.Judge;

 

[dhookom]

Your query needs to include the data types of the parameters. There is a button on the ribbon for this. The SQL would end up something like following with a single parameter:

PARAMETERS Forms!frmEntries.sfmWrittenComments.Form.Company Text ( 255 );
TRANSFORM Sum(ScoresCurrentCompany.[Written Score]) AS [SumOfWritten Score]
SELECT ScoresCurrentCompany.Criterion
FROM ScoresCurrentCompany
WHERE (((ScoresCurrentCompany.Category)=[forms]![frmEntries].[Category]))
GROUP BY ScoresCurrentCompany.Criterion, ScoresCurrentCompany.Category
PIVOT ScoresCurrentCompany.Judge;

Then you need a small table tblXtabColumns as suggested in the FAQ with two records "Written" and "Presentation". This field gets concatenated with your column heading with Judge. Your new column headings would display something like:

Rebecca Price - Presentation	 Rebecca Price - Written	  Sandra Thompson - Presentation	 Sandra Thompson - Written

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

 

[TrekBiker]

Thanks for hanging in there with this, Duane, it's getting very close to what I need. i simplifed it by combining the two Written and Presentation scores for now.

The crosstab you sent does produce results now but totals the Criterion scores for each Company in the Category. This is clearly because the second parameter to select Company isn't there. I added it like this

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

It displays like this

This is perfect, huge thanks!

Final point is I'd like to display this as a subform. I can build a subform but it comes as 6 separate pages, one for each Criterion, like this

Do I have to just call up the crosstab from a button on the form?

 

[dhookom]

OK, back to my first reply (which contained way too much info) "I would create a crosstab query and set it as the source object of a subform control."

Change the Source Object of your subform to:
Source Object: Query.[Your Query Name Here]

This will result in a datasheet view of your query.

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

 

[TrekBiker]

Great thanks, will do this when back from a Tuesday bike ride with our village group, nothing too strenuous, 25 miles. Really appreciate all your help.

 

[dhookom]

Enjoy the ride. I haven’t ridden outdoors yet since there is still a lot of snow on the ground and I don’t have a fatty.

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