使用者常嘗試在資料表中直接顯示「明細 → 小計 → 總計」的階層式統計,但 資料表的職責是儲存原始資料,而非呈現統計結果。Access 2007 的正規解法是使用 報表 搭配 群組與排序 功能,或以 SQL UNION 查詢 產生扁平化結果集供報表繫結。本文詳細說明兩種做法。
報表設計師原生支援多層群組、小計、總計,且支援分頁、格式化、條件顯示,維護性最佳。
qrySalesDetail 選取明細欄位(FullName, Pay),依 FullName, Pay 排序。qrySalesDetail。FullName,設定 依值群組。ControlSource 設為 =Sum([Pay]),標籤設為「小計」。ControlSource 設為 =Sum([Pay]),標籤設為「總計」。若必須在資料表/查詢中直接產生含小計、總計的結果集(如匯出給不支援群組的第三方工具),可使用 UNION ALL 組合三段查詢:
-- 1. 明細
SELECT FullName, Pay FROM tblPay
UNION ALL
-- 2. 小計:在 FullName 後附加「 小計」確保排序在明細之後
SELECT FullName & " 小計" AS Expr1, Sum(Pay) AS PayAll
FROM tblPay
GROUP BY FullName & " 小計"
UNION ALL
-- 3. 總計:使用編碼較大的字元確保排在最後
SELECT "總計" AS Expr1, Sum(Pay) AS PayAll
FROM tblPay
ORDER BY Expr1;
FullName & " 小計":附加字串使小計行的排序鍵值大於同群組的明細行(因空白鍵編碼 32 > 多數中文字首碼),故自然排在群組明細之後。"總計":中文「總」字編碼(Big5: 0xA4A8, Unicode: 0x7E3D)大於常見姓氏首字,故 ORDER BY 會將其置於最末。若資料含特殊字元導致排序錯誤,可改用 "~總計" 或 CHR(255) & "總計" 強制置底。| 維度 | 報表群組 | UNION 查詢 |
|---|---|---|
| 維護性 | 高(視覺化設計,修改版面不動 SQL) | 低(需修改 SQL、注意排序鍵) |
| 效能 | 報表引擎最佳化,大量資料較佳 | 需掃描資料表三次,資料量大較慢 |
| 匯出彈性 | 匯出 PDF/Word/Excel 保留格式 | 產生純資料列,適合匯出 CSV/資料庫 |
| 適用場景 | 列印、預覽、PDF 輸出、使用者互動報表 | API 串接、ETL 中繼、舊系統相容 |
若在 Access 表單中使用 VB DataGrid 等 ActiveX 控制項呈現階層資料,可採用 ADO Data Shaping 服務(SHAPE ... APPEND 語法)產生階層 Recordset,但部署複雜度高、相容性風險大,非必要不建議。
結論:優先使用報表群組功能,符合 Access 設計哲學且維護最輕鬆;僅在必須輸出扁平化資料集給下游系統時,才採用 UNION ALL 查詢並嚴謹設計排序鍵。