Show multiple columns in a cross tabulation Query

Using a cross tabulation, We can display items in a column field, such as [subject] to display [exam scores]. can the [job score] be displayed in the same cross tabulation query at the same time?

For example, the following data
Table 3
+ ----- + -------- + ------- + ------ + ------------- +
| ID | sname | sclass | course | score | homeworkscore |
+ ----- + -------- + ------- + ------ + ------------- +
| 1 | AAA | 3 | language | 50 | 8 |
| 2 | AAA | 3 | mathematics | 83 | 9 |
| 3 | AAA | 3 | English | 65 | 7 |
| 4 | BBB | 3 | language | 86 | 6 |
| 5 | BBB | 3 | mathematics | 95 | 5 |
.......
| 30 | jjjj | 5 | English | 61 | 9 |
| 31 | ll | 5 | language | 80 | 7 |
| 32 | ll | 5 | mathematics | 95 | 9 |
+ ----- + -------- + ------- + ------ + ------------- +

We can get information aboutSubjectCross tabulation Query[Exam Score], You can also get[Job score]

Transform sum (table3.score)
Select table3.sname, table3.sclass
From table3
Group by table3.sname, table3.sclass
Optional table3.course;

+ ------- + ------ + ---- +
| Sname | sclass | mathematics | English | language |
+ ------- + ------ + ---- +
| AAA | 3 | 83 | 65 | 50 |
| BBB | 3 | 95 | 58 | 86 |
| CCC | 3 | 75 | 78 | 92 |
| DDD | 3 | 76 | 77 | 83 |
.....
| Jjjj | 5 | 97 | 61 | 62 |
| Ll | 5 | 95 | 80 |
+ ------- + ------ + ---- +

Transform sum (table3.homeworkscore)
Select table3.sname, table3.sclass
From table3
Group by table3.sname, table3.sclass
Optional table3.course;
+ ------- + ------ + ---- +
| Sname | sclass | mathematics | English | language |
+ ------- + ------ + ---- +
| AAA | 3 | 8 | 6 | 8 |
| BBB | 3 | 6 | 7 | 7 |
| CCC | 3 | 7 | 9 | 9 |
.....
| Jjjj | 5 | 9 | 7 | 5 |
| Ll | 5 | 7 | 8 |
+ ------- + ------ + ---- +

So how can we combine and display these two items?

The first easy way to think of is to save the two separate cross tabulation queries, such as query1 and query2, and then form an inner join query based on these two query1 and query2.
Select query1. *, query2 .*
From query1 inner join query2 on query1.sname = query2.sname;

We cannot predict the number of columns in the cross tabulation query, so we can only select all the fields of query1. * And query2. The result is as follows:
+ -------- + --------- + ------- +
| Q1.sname | q1.sclass | Q1. mathematics | Q1. English | Q1. Chinese | q2.sname | q2.sclass | q2. mathematics | q2. English | q2. Chinese |
+ -------- + --------- + ------- +
| AAA | 3 | 83 | 65 | 50 | AAA | 3 | 8 | 6 | 8 |
| BBB | 3 | 95 | 58 | 86 | BBB | 3 | 6 | 7 | 7 |
| CCC | 3 | 75 | 78 | 92 | CCC | 3 | 7 | 9 | 9 |
| DDD | 3 | 76 | 77 | 83 | DDD | 3 | 7 | 6 | 9 |
| EEE | 3 | 76 | 58 | 60 | EEE | 3 | 5 | 8 | 5 |
| Fff | 4 | 62 | 60 | 85 | fff | 4 | 7 | 7 | 7 |
| Ggg | 4 | 80 | 97 | 81 | ggg | 4 | 6 | 10 | 8 |
| Hhhh | 4 | 88 | 50 | 74 | hhhh | 4 | 8 | 9 | 8 |
| Iiiiiii | 4 | 68 | 50 | 95 | iiiiiii | 4 | 5 | 7 | 8 |
| Jjjj | 5 | 97 | 61 | 62 | jjjj | 5 | 9 | 7 | 5 |
| Ll | 5 | 95 | 80 | ll | 7 | 8 |
+ -------- + --------- + ------- +

Is there any other method?
If your requirements are only for display ,(It seems that cross-tabulation queries can only be performed for display purposes and cannot be updated.), Then multiple fields can be merged into one field through string merging. As follows (Because it is a string, here we change sum () to min ())

Transform min (table3.score & '+' & table3.homeworkscore)
Select table3.sname, table3.sclass
From table3
Group by table3.sname, table3.sclass
Optional table3.course;

+ ------- + ------ + ------- +
| Sname | sclass | mathematics | English | language |
+ ------- + ------ + ------- +
| AAA | 3 | 83 + 8 | 65 + 6 | 50 + 8 |
| BBB | 3 | 95 + 6 | 58 + 7 | 86 + 7 |
| CCC | 3 | 75 + 7 | 78 + 9 | 92 + 9 |
| DDD | 3 | 76 + 7 | 77 + 6 | 83 + 9 |
| EEE | 3 | 76 + 5 | 58 + 8 | 60 + 5 |
| Fff | 4 | 62 + 7 | 60 + 7 | 85 + 7 |
| Ggg | 4 | 80 + 6 | 97 + 10 | 81 + 8 |
| Hhhh | 4 | 88 + 8 | 50 + 9 | 74 + 8 |
| Iiiiiii | 4 | 68 + 5 | 50 + 7 | 95 + 8 |
| Jjjj | 5 | 97 + 9 | 61 + 7 | 62 + 5 |
| Ll | 5 | 95 + 7 | 80 + 8 |
+ ------- + ------ + ------- +

If you want to calculate more, such as importing data to excel, you need to sort out multiple columns according to the requirements of cross tabulation query.

Select sname, sclass, course & ': test score' As scolid, score as vcol
From table3
Union all
Select sname, sclass, course & ': Job score as scolid, homeworkscore as vcol
From table3
+ ----- + ------ + -------------- + ----- +
| Sname | sclass | scolid | vcol |
+ ----- + ------ + -------------- + ----- +
| AAA | 3 | mathematics: test score | 83 |
| AAA | 3 | English: test score | 65 |
| BBB | 3 | language: exam score | 86 |
| BBB | 3 | mathematics: test score | 95 |
| AAA | 3 | language: exam score | 50 |
| AAA | 3 | mathematics: Homework score | 8 |
| AAA | 3 | English: Homework score | 6 |
| BBB | 3 | language: Homework score | 7 |
| BBB | 3 | mathematics: Homework score | 6 |
........
| Ll | 5 | language: Homework score | 8 |
| Ll | 5 | mathematics: Homework score | 7 |
+ ----- + ------ + -------------- + ----- +

Then perform cross-query based on this.
Transform sum (T. vcol)
Select T. sname, T. sclass
From (
Select sname, sclass, course & ': test score' As scolid, score as vcol
From table3
Union all
Select sname, sclass, course & ': Job score as scolid, homeworkscore as vcol
From table3
) T
Group by T. sname, T. sclass
Struct T. scolid;

+ ------- + ------ + ------------- +
| Sname | sclass | mathematics: Homework scores | mathematics: exam scores | English: Homework scores | English: exam scores | language: Homework scores | language: exam scores |
+ ------- + ------ + ------------- +
| AAA | 3 | 8 | 83 | 6 | 65 | 8 | 50 |
| BBB | 3 | 6 | 95 | 7 | 58 | 7 | 86 |
| CCC | 3 | 7 | 75 | 9 | 78 | 9 | 92 |
| DDD | 3 | 7 | 76 | 6 | 77 | 9 | 83 |
| EEE | 3 | 5 | 76 | 8 | 58 | 5 | 60 |
| Fff | 4 | 7 | 62 | 7 | 60 | 7 | 85 |
| Ggg | 4 | 6 | 80 | 10 | 97 | 8 | 81 |
| Hhhh | 4 | 8 | 88 | 9 | 50 | 8 | 74 |
| Iiiiiii | 4 | 5 | 68 | 7 | 50 | 8 | 95 |
| Jjjj | 5 | 9 | 97 | 7 | 61 | 5 | 62 |
| Ll | 5 | 7 | 95 | 8 | 80 |
+ ------- + ------ + ------------- +

This article only discusses how to implement multiple columns of data in the Cross-query table in access. Of course there are many other methods.