Total number of cross tabulation queries

Cross tabulation QueryIt is undoubtedly easy to analyze and process data in use. You can use the Wizard to generate (in the wizard you can choose whether to generate a row total) or directly write this SQL statement according to the access-specific JET-SQL syntax.

Aggregate functions of Transform
Selectstatement
Transform aggfunction
Selectstatement
Struct tfield [IN (value1 [, value2 [,...])]

For example, in the existing table table3, the data is as follows:
+ ---- + --------- + ------- + -------- + ------ + ------------- +
| ID | sname | sclass | course | score | homeworkscore |
+ ---- + --------- + ------- + -------- + ------ + ------------- +
| 1 | AAA | 3 | language | 50 | 76 |
| 2 | AAA | 3 | mathematics | 83 | 77 |
| 3 | AAA | 3 | English | 65 | 60 |
| 4 | BBB | 3 | language | 86 | 72 |
| 5 | BBB | 3 | mathematics | 95 | 57 |
.......
| 31 | ll | 5 | language | 80 | 75 |
| 32 | ll | 5 | mathematics | 95 | 70 |
+ ---- + --------- + ------- + -------- + ------ + ------------- +
You can use the Wizard to obtain the tables for each person as follows:
+ -------- + ------- + --------------- + ------- +
| Sname | sclass | Total of score | mathematics | English | language |
+ -------- + ------- + --------------- + ------- +
| AAA | 3 | 198 | 83 | 65 | 50 |
| BBB | 3 | 239 | 95 | 58 | 86 |
......
| Ll | 5 | 175 | 95 | 80 |
+ -------- + ------- + --------------- + ------- +

The corresponding SQL statement is as follows:
Transform sum (table3.score) as scoreofsum
Select table3.sname, table3.sclass, sum (table3.score) as [total of Score]
From table3
Group by table3.sname, table3.sclass
Optional table3.course;

For the description of this SQL statement, you can refer to the detailed description in the help manual of access.
If you want to control the display sequence of a subject, you can try this syntax.Struct tfield [IN (value1 [, value2 [,...])]

These are common cross-table queries. In the United States, the query generated by the wizard has a total of rows, but no total columns. Due to the limitations of transform's own functions, you cannot directly generate the column aggregate operation (the total operation we mentioned here includes average, maximum, and minimum, which are not described below ). However, we can achieve this through Union.

Ideas: Directly append the total rows to table 3 and then cross the rows.

For example, if Table 3 data can form the following records
+ ---- + --------- + ------- + -------- + ------ + ------------- +
| ID | sname | sclass | course | score | homeworkscore |
+ ---- + --------- + ------- + -------- + ------ + ------------- +
| 1 | AAA | 3 | language | 50 | 76 |
| 2 | AAA | 3 | mathematics | 83 | 77 |
| 3 | AAA | 3 | English | 65 | 60 |
...
| 31 | ll | 5 | language | 80 | 75 |
| 32 | ll | 5 | mathematics | 95 | 70 |
| Average | English | 86 |
| Average | mathematics | 77 |
| Average | English | 99 |
+ ---- + --------- + ------- + -------- + ------ + ------------- +

In this way, we can use transform to implement it.

1. Generate the total. You can generate the total query through the wizard or by yourself.
Select Course, AVG (score)
From table3
Group by course

+ ------- + ----------------- +
| Course | expr1001 |
+ ------- + ----------------- +
| Mathematics | 81.3636363636364 |
| English | 65.4 |
| Chinese | 77.0909090909091 |
+ ------- + ----------------- +

2. Use union to generate a data source for cross tabulation query. (Here we use Union all. For more information about the Union syntax, see help. We also use 'Total' as sname, null as sclass generates two constant columns to ensure that the columns of the two sets of Union match .)

The hidden content of this post must be replied before browsing.

Select sname, sclass, course, score
From table3
Union all
Select 'Total' as sname, null as sclass, course, AVG (score)
From table3
Group by course

+ ------- + -------- + ------- + ----- +
| Sname | sclass | course | score |
+ ------- + -------- + ------- + ----- +
| AAA | 3 | mathematics | 83 |
| AAA | 3 | English | 65 |
.....
| Ll | 5 | mathematics | 95 |
| Total | mathematics | 81.36 |
| Total | English | 65.4 |
| Total | | 77.09 |
+ ------- + -------- + ------- + ----- +

3. Replace the query with the one opened cross query to replace the original table3.
Replace all table3. with T.

Transform sum (T. Score) as scoreofsum
Select T. sname, T. sclass, sum (T. Score) as [total of Score]
From table3
Group by T. sname, T. sclass
Fail T. Course;

Then, you need to reply to the hidden content of the from table3 variable cost post before browsing.

Transform sum (T. Score) as scoreofsum
Select T. sname, T. sclass, sum (T. Score) as [total of Score]
From (select sname, sclass, course, score
From table3
Union all
Select 'Total' as sname, null as sclass, course, AVG (score)
From table3
Group by course) T
Group by T. sname, T. sclass
Fail T. Course;

The result is as follows:
+ -------- + ------ + ----- +
| Sname | sclass | Total | mathematics | English | language |
+ -------- + ------ + ----- +
| AAA | 3 | 198 | 83 | 65 | 50 |
| BBB | 3 | 239 | 95 | 58 | 86 |
.......
| Jjjj | 5 | 220 | 97 | 61 | 62 |
| Ll | 5 | 175 | 95 | 80 |
| Total | 223.85 | 81.36 | 65.4 | 77.09 |
+ -------- + ------ + ----- +

If we want to add the subtotal for each class
Then, you can view the hidden content in this post only after you reply to the total average value of each class on the union operation.

Select 'subtotal' as sname, sclass, course, AVG (score)
From table3
Group by course, sclass
This is changed
Transform sum (T. Score) as scoreofsum
Select T. sname, T. sclass, sum (T. Score) as [total of Score]
From (select sname, sclass, course, score
From table3
Union all
Select 'subtotal' as sname, sclass, course, AVG (score)
From table3
Group by course, sclass
Union all
Select 'Total' as sname, null as sclass, course, AVG (score)
From table3
Group by course
) T
Group by T. sname, T. sclass, (T. sclass = 'subtotal'), (T. sclass = 'Total ')
Order by (T. sclass = 'Total') DESC, (T. sclass = 'subtotal') DESC, sclass
Certificate T. Course

Order by (T. sclass = 'Total') DESC, (T. sclass = 'subtotal') DESC and sclass are used to control the sorting to put subtotal and total at the end.
+ ---------- + -------- + --------- + ------ +
| Sname | sclass | Total of | mathematics | English | language |
+ ---------- + -------- + --------- + ------ +
| AAA | 3 | 198 | 83 | 65 | 50 |
| BBB | 3 | 239 | 95 | 58 | 86 |
....
| Subtotal | 3 | 222.4 | 81 | 67.2 | 74.2 |
......
| Ll | 5 | 175 | 95 | 80 |
| Subtotal | 5 | 228 | 96 | 61 | 71 |
| Total | 223.8545 | 81.363 | 65.4 | 77.090 |
+ ---------- + -------- + --------- + ------ +

Conclusion:
Obviously, with flexible SQL statement design, we can implement a variety of functions in VBA programs. In practical use, we need to balance various solutions to find the best application. Sometimes it is more efficient to use a program, sometimes it is more convenient to use the query, and sometimes it is easier to even jump out of access and use Excel.