Solved Change column order for temporary table (1 Viewer)

Martynwheeler

Member
Local time
Today, 06:55
Joined
Jan 19, 2024
Messages
82
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:

1706448922685.png


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

Well-known member
Local time
Today, 07:55
Joined
Feb 7, 2020
Messages
2,002
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.
 

Martynwheeler

Member
Local time
Today, 06:55
Joined
Jan 19, 2024
Messages
82
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.
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:
1706456306316.png

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

I have included a second table for the column headings

1706456401332.png


in terms of sql this is:
SQL:
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:
1706456541159.png


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

Member
Local time
Today, 06:55
Joined
Jan 19, 2024
Messages
82
Sorry, scrub that, I just figured it out! got to use the IIf conditional. which gives a true or false value.
 
Last edited:

Users who are viewing this thread

Top Bottom