Solved Change column order for temporary table (1 Viewer)

[Martynwheeler]

I have a crosstab query of student marks for tests (test_id's are 1, 2, 7). In order to display the data I need to generate some calculated values from the score (grade and a unified weighted score). I don't think I can add these extra columns to the crosstab itself (correct me if I am wrong), so I thought about creating a temporary table from the crosstab and adding extra columns.

I have done this so far:

Is there anyway to resort the headers of the table so that they are grouped by the test_id? Or is there a much better approach to what I am doing altogther? I am a bit of an access novice.

Thank you

 

[ebs17]

The sorting of the columns in pivoting is done automatically, alphanumeric for text content.
So you could specifically create these additional columns in the cross table query, see Multiple sets of values in the following article.

Microsoft Access tips: Crosstab query tips

Crosstab query tips for Microsoft Access

allenbrowne.com

 

[Martynwheeler]

The sorting of the columns in pivoting is done automatically, alphanumeric for text content.
So you could specifically create these additional columns in the cross table query, see Multiple sets of values in the following article.

Microsoft Access tips: Crosstab query tips

Crosstab query tips for Microsoft Access

allenbrowne.com

That seems to be what I want, but I can't quite work out the syntax.

Here is my query that I am basing the crosstab on:

I want both the columns at the end for each student for each test

I have included a second table for the column headings

in terms of sql this is:

TRANSFORM Max(qselStudentMarks.mark) AS MaxOfmark
SELECT qselStudentMarks.student_id, qselStudentMarks.class_id
FROM qselStudentMarks, Student_CrossTabCols
GROUP BY qselStudentMarks.student_id, qselStudentMarks.class_id
PIVOT qselStudentMarks.assessment_id & "-" & [FieldName];

I can't work out what to put in the transform part of the command?

it is currently giving me:

i.e putting the score into both headings. How can I get percent in one column and then score in the next?

Thanks

Martyn

 

[Martynwheeler]

Sorry, scrub that, I just figured it out! got to use the IIf conditional. which gives a true or false value.