交叉表查詢無疑有使用中可以很方便的進行數據的分析處理。你可以通過向導來生成（在向導中你可以選擇是否生成行合計）或者直接按照這個
Access特有JET-SQL語法來寫這個SQL語句。
TRANSFORM合計函數
selectstatement
TRANSFORM aggfunction
selectstatement
PIVOT pivotfIEld [IN (value1[, value2[, ...]])]
比如現有表 table3, 數據如下
+----+---------+-------+--------+------+-------------+
|id |sName |sClass |Course |Score |homeworkScore|
+----+---------+-------+--------+------+-------------+
|1 |AAA |3 |語文 |50 |76 |
|2 |AAA |3 |數學 |83 |77 |
|3 |AAA |3 |英語 |65 |60 |
|4 |BBB |3 |語文 |86 |72 |
|5 |BBB |3 |數學 |95 |57 |
.......|31 |LL |5 |語文 |80 |75 |
|32 |LL |5 |數學 |95 |70 |
+----+---------+-------+--------+------+-------------+
可以用向導得到一個每人的成績表如下
+--------+-------+---------------+-------+-------+-------+
|sName |sClass |Total Of Score |數學 |英語 |語文 |
+--------+-------+---------------+-------+-------+-------+
|AAA |3 |198 |83 |65 |50 |
|BBB |3 |239 |95 |58 |86 |
......|LL |5 |175 |95 | |80 |
+--------+-------+---------------+-------+-------+-------+
它對應的SQL語句如下：
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
PIVOT Table3.Course;
關於這個SQL語句的說明，你可以自已查閱一下
Access自帶的幫助手冊中的詳細說明。
如果你想控制科目的顯示順序，可以試一下這個語法的作用。PIVOT pivotfIEld [IN (value1[, value2[, ...]])]
以上是交叉表查詢的常見用法。美中不足，這個由向導生成的查詢雖然有了行合計，但沒有列合計。由於TRANSFORM 自身功能的限制無法直接生成列合計運算(我們這裡所說的合計運算包括平均/最大/最小等，以下均不再說明)。但我們可以通過UNION聯合來實現。
思路：直接在table3的數據中追加上合計行然後再進行交叉。
比如如果table3的數據能形成如下記錄
+----+---------+-------+--------+------+-------------+
|id |sName |sClass |Course |Score |homeworkScore|
+----+---------+-------+--------+------+-------------+
|1 |AAA |3 |語文 |50 |76 |
|2 |AAA |3 |數學 |83 |77 |
|3 |AAA |3 |英語 |65 |60 |
...
|31 |LL |5 |語文 |80 |75 |
|32 |LL |5 |數學 |95 |70 |
| |Average | |英語 |86 | |
| |Average | |數學 |77 | |
| |Average | |英語 |99 | |
+----+---------+-------+--------+------+-------------+
這樣我們就可以利用 TRANSFORM 來實現了。
1. 生成合計，你可以通過向導或自己生成這個合計的查詢
select course,avg(score)
from table3
group by course
+-------+-----------------+
|course |Expr1001 |
+-------+-----------------+
|數學 |81.3636363636364 |
|英語 |65.4 |
|語文 |77.0909090909091 |
+-------+-----------------+
2. 利用UNION生成交叉表查詢的數據源。(這裡我們用了UNION ALL,關於UNION的語法說明請自行查閱幫助，同樣我們利用 'Total' as sName,null as sClass 生成了兩個常數列以保證UNION的兩個集合的列數相匹配。)
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 |數學 |83 |
|AAA |3 |英語 |65 |
.....
|LL |5 |數學 |95 |
|Total | |數學 |81.36|
|Total | |英語 |65.4 |
|Total | |語文 |77.09|
+-------+--------+-------+-----+
3. 把這個查詢代入到一開的那個交叉查詢中，替代原來的table3.
把把所有的table3. 換成 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
PIVOT t.Course;
然後再把 from table3 變成
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
PIVOT t.Course;
結果如下
+--------+------+------+-----+-----+-----+
|sName |sClass|Total |數學 |英語 |語文 |
+--------+------+------+-----+-----+-----+
|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|
+--------+------+------+-----+-----+-----+
如果我們想再加上每個班的小計
那麼就再union上每個班的合計平均值
select 'subtotal' as sName,sClass,course,avg(score)
from table3
group by course,sClass
這樣改為
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
PIVOT t.Course
上面用了 order by (t.sClass='Total') desc,(t.sClass='subtotal') desc,sClass 來控制排序，以把subtotal, total 放在最後。
+----------+--------+---------+------+------+------+
|sName |sClass |Total Of |數學 |英語 |語文 |
+----------+--------+---------+------+------+------+
|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|
+----------+--------+---------+------+------+------+
結束語：
顯然通過靈活的SQL語句設計我們可以實現多種需要有VBA程序中實現功能。在實際運用中我們需要在各種方案之間來平衡以找到最佳的應用。有時候用程序的效率比較好，有些時候用查詢的比較方便，有些時候甚至跳出Access用Excel可能更容易。
