Show multiple columns in a cross tabulation Query

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.