本文說明如何在稱為匯總函數 Access 中使用函數類型,加總查詢結果集中的數據。 本文也簡要說明如何使用其他匯總函數,例如 COUNT 和 AVG 來計算或平均結果集中的值。 此外,本文還說明如何使用合計列,這是您在 Access 中用來加總數據而不需要變更查詢設計的功能。
您想要做什麼?
瞭解加總數據的方式
您可以使用稱為匯總函數的函數類型,加總查詢中的數字數據行。 匯總函數會對數據域執行計算,並傳回單一值。 Access 提供各種匯總函數,包括 Sum、 Count、 Avg (,用於計算平均值) 、 最小 值和 最大值。 您可以將 Sum 函數 新增至查詢來加總數據、使用 Count 函數計算數據,依此類操作。
此外, Access 提供數種方式將 Sum 和其他匯總函數新增至查詢。 您可以:
-
在 [數據工作表檢視] 中開啟查詢並新增合計列。 合計列是 Access中的一項功能,可讓您在查詢結果集的一或多個欄中使用匯總函數,而不需要變更查詢的設計。
-
建立合計查詢。 合計查詢會計算各記錄群組中小計;合計列會計算一個或多個欄的總計 (數據) 字段。 例如,如果您想要依縣/市或按季小計所有銷售額,您可以使用合計查詢,依所要的類別將記錄分組,然後再加總銷售數位。
-
建立交叉表查詢。 交叉表查詢是一種特殊類型的查詢,會以類似 Excel 工作表的網格線顯示結果。 交叉表查詢會摘要您的值,然後依兩組事實加以分組:一組在側 (列標題) ,另一組則在最上方 (欄標題) 。 例如,您可以使用交叉表查詢來顯示過去三年各城市的銷售總額,如下表所示:
城市 |
2003 |
2004 |
2005 |
---|---|---|---|
巴黎 |
254,556 |
372,455 |
467,892 |
雪梨 |
478,021 |
372,987 |
276,399 |
Jakarta |
572,997 |
684,374 |
792,571 |
... |
... |
... |
... |
附註: 本文中的操作說明章節強調使用 Sum 函數 ,但請記住,您可以在合計列和查詢中使用其他匯總函數。 如需使用其他匯總函數的詳細資訊,請參閱本文稍後的 匯總函數參照 一節。
如需使用其他匯總函數方式的詳細資訊,請參閱 在數據工作表中顯示欄合計一文。
下列各節中的步驟說明如何新增合計列、使用合計查詢來加總各群組的數據,以及如何使用跨群組及時間間隔小計數據的交叉表查詢。 繼續進行時,請記住,許多匯總函數只適用於設定為特定數據類型的欄位中的數據。 例如, SUM 函數只適用於設定為 [數位]、[小數] 或 [貨幣] 數據類型的欄位。 如需每個函數所需的數據類型詳細資訊,請參閱本文稍後的 匯總函數參照一節。
如需數據類型的一般資訊,請參閱 修改或變更字段的數據類型集一文。
準備一些範例數據
本文中的操作說明章節提供範例數據的表格。 使用方法步驟使用範例數據表,以協助您了解匯總函數的運作方式。 您也可以視需要將範例數據表新增到新的或現有的資料庫。
Access 提供數種方式將這些範例數據表新增至資料庫。 您可以手動輸入數據、將每個數據表複製到 Excel 等電子表格程式,然後將工作表匯入 Access,或者將數據貼到 [記事本] 等文字編輯器中,然後從產生的文字檔匯入數據。
本節中的步驟說明如何在空白數據工作表中手動輸入數據,以及如何將範例數據表複製到電子表格程式,然後將這些數據表匯入 Access。 如需建立及匯入文字數據的詳細資訊,請參閱匯入 或連結至文本文件中的數據一文。
本文中的操作步驟使用下表。 使用下列資料表建立範例資料:
[ 類別] 資料 表:
類別 |
---|
娃娃 |
遊戲與拼圖 |
藝術與框架 |
視訊遊戲 |
DVD 和電影 |
模型與愛好 |
運動 |
[ 產品] 數據表:
產品名稱 |
價格 |
類別 |
---|---|---|
[程序設計師] 動作圖 |
$12.95 |
娃娃 |
享受 C# (全家人的遊戲版面遊戲) |
$15.85 |
遊戲與拼圖 |
關係資料庫圖 |
$22.50 |
藝術與框架 |
魔幻電腦晶元 (500 塊) |
$32.65 |
遊戲與拼圖 |
訪問! 遊戲! |
$22.95 |
遊戲與拼圖 |
計算機極客與神話生物 |
$78.50 |
視訊遊戲 |
練習計算機格子! The DVD! |
$14.88 |
DVD 和電影 |
Ultimate 飛披薩 |
$36.75 |
運動 |
外部 5.25 吋磁碟驅動器 (1/4 Scale) |
$65.00 |
模型與愛好 |
非宏指令圖 |
$78.88 |
娃娃 |
黑暗 |
$53.33 |
視訊遊戲 |
建立您自己的鍵盤 |
$77.95 |
模型與愛好 |
[ 訂單] 數據 表:
訂單日期 |
出貨日期 |
出貨城市 |
運送費用 |
---|---|---|---|
11/14/2005 |
11/15/2005 |
Jakarta |
$55.00 |
11/14/2005 |
11/15/2005 |
雪梨 |
$76.00 |
11/16/2005 |
11/17/2005 |
雪梨 |
$87.00 |
11/17/2005 |
11/18/2005 |
Jakarta |
$43.00 |
11/17/2005 |
11/18/2005 |
巴黎 |
$105.00 |
11/17/2005 |
11/18/2005 |
斯圖加特 |
$112.00 |
11/18/2005 |
11/19/2005 |
維也納 |
$215.00 |
11/19/2005 |
11/20/2005 |
邁阿密 |
$525.00 |
11/20/2005 |
11/21/2005 |
維也納 |
$198.00 |
11/20/2005 |
11/21/2005 |
巴黎 |
$187.00 |
11/21/2005 |
11/22/2005 |
雪梨 |
$81.00 |
11/23/2005 |
11/24/2005 |
Jakarta |
$92.00 |
[ 訂單詳細數據] 資料表:
訂單識別碼 |
產品名稱 |
產品識別碼 |
單價 |
數量 |
Discount |
---|---|---|---|---|---|
1 |
建立您自己的鍵盤 |
12 |
$77.95 |
9 |
5% |
1 |
非宏指令圖 |
2 |
$78.88 |
4 |
7.5% |
2 |
練習計算機格子! The DVD! |
7 |
$14.88 |
6 |
4% |
2 |
魔幻電腦晶片 |
4 |
$32.65 |
8 |
0 |
2 |
計算機極客與神話生物 |
6 |
$78.50 |
4 |
0 |
3 |
訪問! 遊戲! |
5 |
$22.95 |
5 |
15% |
4 |
程序設計師動作圖 |
1 |
$12.95 |
2 |
6% |
4 |
Ultimate 飛披薩 |
8 |
$36.75 |
8 |
4% |
5 |
外部 5.25 吋磁碟驅動器 (1/4 Scale) |
9 |
$65.00 |
4 |
10% |
6 |
關係資料庫圖 |
3 |
$22.50 |
12 |
6.5% |
7 |
黑暗 |
11 |
$53.33 |
6 |
8% |
7 |
關係資料庫圖 |
3 |
$22.50 |
4 |
9% |
附註: 請記住,在一般資料庫中,訂單詳細數據表只會包含 [產品標識符] 欄位,而非 [產品名稱] 字段。 範例數據表使用 [產品名稱] 字段,讓數據更容易閱讀。
手動輸入範例資料
-
在 [建立] 索引標籤的 [資料表] 群組中,按一下 [資料表]。
Access 會在您的資料庫中新增新的空白資料表。
附註: 如果您開啟新的空白資料庫,則無需按照此步驟進行。不過,每當您需要在資料庫中新增資料表時,則要按照此步驟進行。
-
按兩下標題列中的第一個儲存格,然後在範例數據表中輸入欄位名稱。
根據預設,Access 會將標題欄中的空白欄位表示為 [ 新增字段] 文字,如下所示:
-
使用箭頭鍵移至下一個空白標題單元格,然後輸入第二個功能變數名稱 (您也可以按 TAB 鍵或按兩下新單元格) 。 重複此步驟,直到您輸入所有域名為止。
-
在範例資料表中輸入資料。
當您輸入資料時,Access 會推斷每個欄位的資料類型。 如果您是關係資料庫的新使用者,您應該為數據表中的每個欄位設定特定的數據類型,例如 [數位]、[文字] 或 [日期/時間]。 設定數據類型有助於確保正確輸入數據,也有助於避免錯誤,例如在計算中使用電話號碼。 針對這些範例數據表,您應該讓 Access 推斷數據類型。
-
當您完成輸入數據時,按兩下 [ 儲存]。
鍵盤快捷方式按 CTRL+S。
[另存新檔] 對話方塊隨即出現。
-
在 [ 表格名稱] 方 塊中,輸入範例數據表的名稱,然後按兩下 [ 確定]。
您使用每個範例數據表的名稱,因為操作說明區段中的查詢會使用這些名稱。
-
重複這些步驟,直到您建立本節開頭列出的每個範例數據表為止。
如果您不想手動輸入數據,請遵循下一個步驟,將數據複製到電子表格檔案,然後將電子表格檔案中的數據匯入 Access。
建立範例工作表
-
啟動您的電子錶格程式,並建立新的空白檔案。 如果您使用 Excel,預設會建立新的空白活頁簿。
-
複製上面提供的第一個範例表格,並貼到第一個工作表,從第一個單元格開始。
-
使用電子錶格程式所提供的技術,重新命名工作表。 將工作表命名為與範例數據表相同的名稱。 例如,如果範例數據表名為 [ 類別],請為您的工作表命名相同的名稱。
-
重複步驟 2 和 3,將每個範例數據表複製到空白工作表,然後重新命名工作表。
附註: 您可能需要將工作表新增至電子表格檔案。 如需執行該工作的資訊,請參閱電子錶格程序的說明。
-
將活頁簿儲存到您的電腦或網路中方便使用的位置,然後進行下一組步驟。
從工作表建立資料庫資料表
-
在 [外部數據] 索引標籤的 [匯入 & 連結] 群組中,按兩下 [從檔案 > 新數據源] > Excel。
[ 取得外部數據 - Excel 電子表格 ] 對話框隨即出現。
-
按兩下 [瀏覽],開啟您在上述步驟中建立的電子表格檔案,然後按兩下 [ 確定]。
[匯入試算表精靈] 隨即啟動。
-
根據預設,如果您遵循前一節) 中的步驟,精靈會選取活頁簿 (客戶工作表 中的第一個工作表,而來自工作表的數據會出現在精靈頁面的下方區段中。 按一下 [下一步]。
-
在精靈的下一個頁面上,按兩下 [ 第一列包含欄標題],然後按兩下 [ 下一步]。
-
或者,在下一頁使用 [ 欄位選項 ] 底下的文字框和清單來變更功能變數名稱和數據類型,或省略匯入作業中的欄位。 否則,請按一下 [下一步]。
-
將 [ 讓 Access 新增主鍵 ] 選項保留選取狀態,然後按 [ 下一步]。
-
根據預設,Access 會將工作表名稱套用到您的新資料表。 接受名稱或輸入其他名稱,然後按兩下 [ 完成]。
-
重複步驟 1 到 7,直到您從活頁簿中的每個工作表建立表格為止。
重新命名主鍵欄位
附註: 當您匯入工作表時,Access 會自動將主鍵數據行新增至每個數據表,而且根據預設,Access 會將該數據行命名為「標識符」,並將其設定為 [自動編號] 數據類型。 其中的步驟說明如何重新命名每個主鍵欄位。 這麼做有助於清楚識別查詢中的所有欄位。
-
在 [導航窗格] 中,以滑鼠右鍵按下您在上述步驟中建立的每一個數據表,然後按兩下 [ 設計檢視]。
-
針對每個數據表,找出主鍵欄位。 根據預設,Access 會為每個字段 標識符命名。
-
在每個主鍵欄位的 [ 功能變數名稱 ] 欄中,新增資料表的名稱。
例如,您要將 [類別] 資料表的 [標識符] 字段重新命名為 「類別標識元」,並將 [訂單] 數據表的欄位重新命名為 [訂單標識符]。 若是 [訂單詳細數據] 資料表,請將字段重新命名為「詳細數據識別碼」。 若是 [產品] 數據表,請將字段重新命名為「產品標識碼」。
-
儲存變更。
每當本文中出現範例數據表時,都會包含主鍵欄位,並使用上述步驟重新命名字段。
使用合計列加總數據
您可以在 [數據工作表檢視] 中開啟查詢、新增數據列,然後選取您要使用的匯總函數,例如 [加總]、[ 最小]、[ 最大] 或 [Avg],藉此將 [合計] 列新增至查詢。 本節中的步驟說明如何建立基本選取查詢及新增合計列。 您不需要使用上一節所述的範例數據表。
建立基本選取查詢
-
在 [建立] 索引標籤的 [查詢] 群組中,按一下 [查詢設計]。
-
按兩下您要用於查詢的數據表。
在查詢設計工具的上方區段中,選取的數據表會顯示為視窗。
-
按兩下您要用於查詢的數據表欄位。
您可以包含包含描述性數據的欄位,例如名稱和描述,但您必須包含包含數位或貨幣數據的欄位。
每個欄位都會出現在設計網格線的儲存格中。
-
按兩下 [執行 ] 以執行查詢。
查詢結果集會顯示在 [數據工作表檢視] 中。
-
或者,切換到 [設計視圖] 並調整查詢。 若要這麼做,請以滑鼠右鍵按下查詢的檔索引標籤,然後按兩下 [ 設計檢視]。 接著您可以視需要新增或移除資料表欄位來調整查詢。 若要移除欄位,請選取設計網格線中的欄,然後按 DELETE。
-
儲存您的查詢。
新增合計列
-
確認您的查詢已在 [數據工作表檢視] 中開啟。 若要這麼做,請以滑鼠右鍵按下查詢的檔索引標籤,然後按兩下 [ 資料工作表檢視]。
-或-
在 [導航窗格] 中,按兩下查詢。 這會執行查詢,並將結果載入數據工作表。
-
在 [常用] 索引標籤上,按一下 [記錄] 群組中的 [合計]。
新的 [合計 ] 列會出現在數據工作表中。
-
在 [ 合計] 列中,按兩下要加總之欄位中的儲存格,然後從清單中選取 [ 加總 ]。
隱藏合計列
-
在 [常用] 索引標籤上,按一下 [記錄] 群組中的 [合計]。
如需使用合計列的詳細資訊,請參閱 在數據工作表中顯示欄合計一文。
使用查詢計算總計
總計是欄中所有值的總和。 您可以計算數種類型的總計,包括:
-
將單一欄中的值加總的簡單總計。 例如,您可以計算總運費。
-
加總多個數據行中值的計算總計。 例如,您可以將數個專案的成本乘以訂購的項目數,然後計算結果值,藉此計算總銷售額。
-
排除部分記錄的總計。 例如,您可以計算僅上週五的總銷售額。
下列各節中的步驟說明如何建立每種類型的總計。 步驟會使用 [訂單] 和 [訂單詳細數據] 數據表。
[訂單] 數據表
訂單識別碼 |
訂單日期 |
出貨日期 |
出貨城市 |
運送費用 |
---|---|---|---|---|
1 |
11/14/2005 |
11/15/2005 |
Jakarta |
$55.00 |
2 |
11/14/2005 |
11/15/2005 |
雪梨 |
$76.00 |
3 |
11/16/2005 |
11/17/2005 |
雪梨 |
$87.00 |
4 |
11/17/2005 |
11/18/2005 |
Jakarta |
$43.00 |
5 |
11/17/2005 |
11/18/2005 |
巴黎 |
$105.00 |
6 |
11/17/2005 |
11/18/2005 |
斯圖加特 |
$112.00 |
7 |
11/18/2005 |
11/19/2005 |
維也納 |
$215.00 |
8 |
11/19/2005 |
11/20/2005 |
邁阿密 |
$525.00 |
9 |
11/20/2005 |
11/21/2005 |
維也納 |
$198.00 |
10 |
11/20/2005 |
11/21/2005 |
巴黎 |
$187.00 |
11 |
11/21/2005 |
11/22/2005 |
雪梨 |
$81.00 |
12 |
11/23/2005 |
11/24/2005 |
Jakarta |
$92.00 |
[訂單詳細數據] 資料表
詳細標識碼 |
訂單識別碼 |
產品名稱 |
產品識別碼 |
單價 |
數量 |
Discount |
---|---|---|---|---|---|---|
1 |
1 |
建立您自己的鍵盤 |
12 |
$77.95 |
9 |
0.05 |
2 |
1 |
非宏指令圖 |
2 |
$78.88 |
4 |
0.075 |
3 |
2 |
練習計算機格子! The DVD! |
7 |
$14.88 |
6 |
0.04 |
4 |
2 |
魔幻電腦晶片 |
4 |
$32.65 |
8 |
0.00 |
5 |
2 |
計算機極客與神話生物 |
6 |
$78.50 |
4 |
0.00 |
6 |
3 |
訪問! 遊戲! |
5 |
$22.95 |
5 |
0.15 |
7 |
4 |
程序設計師動作圖 |
1 |
$12.95 |
2 |
0.06 |
8 |
4 |
Ultimate 飛披薩 |
8 |
$36.75 |
8 |
0.04 |
9 |
5 |
外部 5.25 吋磁碟驅動器 (1/4 Scale) |
9 |
$65.00 |
4 |
0.10 |
10 |
6 |
關係資料庫圖 |
3 |
$22.50 |
12 |
0.065 |
11 |
7 |
黑暗 |
11 |
$53.33 |
6 |
0.08 |
12 |
7 |
關係資料庫圖 |
3 |
$22.50 |
4 |
0.09 |
計算簡單的總計
-
在 [建立] 索引標籤的 [查詢] 群組中,按一下 [查詢設計]。
-
按兩下您要用於查詢的數據表。
如果您使用範例數據,請按兩下 [訂單] 資料表。
數據表會出現在查詢設計工具上方區段的視窗中。
-
按兩下您要加總的欄位。 請確定欄位設定為[數位] 或 [貨幣] 資料類型。 如果您嘗試加總非數值欄位中的值,例如 [文字] 字段,Access 會在您嘗試執行查詢時, 在準則表達式錯誤訊息中顯示數據類型不相符 。
如果您使用範例數據,請按兩下 [運費] 欄。
如果您想要計算這些欄位的總計,您可以將額外的數值欄位新增至網格線。 合計查詢可以計算多個數據行的總計。
-
在 [ 查詢設計] 索引 標籤的 [ 顯示/隱藏 ] 群組中,按兩下 [ 合計]。
[合計] 列會出現在設計網格線中,而 [群組依據] 會出現在 [運費] 欄的單元格中。
-
將 [ 合計 ] 列中儲存格中的值變更為 [加總]。
-
按兩下 [執行 ] 以執行查詢,並在 [資料工作表檢視] 中顯示結果。
提示: 請注意,Access 會在您加總的功能變數名稱開頭附加 “SumOf”。 若要將欄標題變更為更有意義的專案,例如 [ 總運送],請切換回 [設計檢視],然後按兩下設計網格線中 [運費] 欄的 [ 欄位 ] 列。 將游標放在 [ 運費 ] 旁邊,然後輸入 「 總交貨量」字樣,後面接著冒號,像這樣: 總運費:運費。
-
或者,儲存查詢並關閉查詢。
計算排除部分記錄的總計
-
在 [建立] 索引標籤的 [查詢] 群組中,按一下 [查詢設計]。
-
按兩下 [訂單] 資料表和 [訂單詳細數據] 資料表。
-
將 [訂單日期] 字段從 [訂單] 數據表新增到查詢設計網格線的第一欄。
-
在第一欄的 [ 準則] 列中,輸入 日期 () -1。 該表達式會將當天的記錄從計算總計中排除。
-
接下來,建立一欄來計算每筆交易的銷售金額。 在格線第二欄的 [字段 ] 列中輸入下列表示式:
總銷售額: (1-[訂單詳細數據]。[Discount]/100) * ([訂單詳細數據]。[單價]*[訂單詳細數據]。[數量])
請確定您的表示式參照字位設定為 [數位] 或 [貨幣] 數據類型。 如果您的表達式參照到設定為其他數據類型的欄位,當您嘗試執行查詢時,Access 會在準則表示式中顯示訊息數據類型不相符 的訊息。
-
在 [ 查詢設計] 索引 標籤的 [ 顯示/隱藏 ] 群組中,按兩下 [ 合計]。
[合計] 列會出現在設計網格線中,而 [群組依據] 會出現在第一欄和第二欄。
-
在第二欄中,將 [ 合計 ] 列單元格中的值變更為 [加總]。 Sum 函數會新增個別銷售數位。
-
按兩下 [執行 ] 以執行查詢,並在 [資料工作表檢視] 中顯示結果。
-
將查詢儲存為 [每日銷售額]。
附註: 下次在 [設計視圖] 中開啟查詢時,您可能會注意到 [總銷售額] 數據行 [ 欄位 ] 和 [ 合計 ] 列中所指定的值稍有變更。 表達式會顯示在 Sum 函數內,而 [ 合計 ] 列會顯示 Expression ,而非 Sum。
例如,如果您使用範例數據並建立查詢 (如上述步驟) 所示,您會看到:
總銷售額:總 ( (1-[訂單詳細數據]。折扣/100) * ([訂單詳細數據]。Unitprice*[Order Details]。數量) )
使用合計查詢計算群組總計
本節中的步驟說明如何建立合計查詢,以計算各數據群組的小計。 繼續進行時請記住,根據預設,合計查詢只能包含包含您群組數據的欄位,例如「類別」欄位,以及包含您要加總之數據的欄位,例如「銷售」字段。 合計查詢不能包含描述類別中專案的其他欄位。 如果您想要查看描述性數據,可以建立第二個選取查詢,將合計查詢中的欄位與其他數據欄位合併。
本節中的步驟說明如何建立合計,並選取識別每個產品總銷售額所需的查詢。 步驟假設使用下列範例數據表:
[產品] 數據表
產品識別碼 |
產品名稱 |
價格 |
類別 |
---|---|---|---|
1 |
[程序設計師] 動作圖 |
$12.95 |
娃娃 |
2 |
享受 C# (全家人的遊戲版面遊戲) |
$15.85 |
遊戲與拼圖 |
3 |
關係資料庫圖 |
$22.50 |
藝術與框架 |
4 |
魔幻電腦晶元 (500 塊) |
$32.65 |
藝術與框架 |
5 |
訪問! 遊戲! |
$22.95 |
遊戲與拼圖 |
6 |
計算機極客與神話生物 |
$78.50 |
視訊遊戲 |
7 |
練習計算機格子! The DVD! |
$14.88 |
DVD 和電影 |
8 |
Ultimate 飛披薩 |
$36.75 |
運動 |
9 |
外部 5.25 吋磁碟驅動器 (1/4 Scale) |
$65.00 |
模型與愛好 |
10 |
非宏指令圖 |
$78.88 |
娃娃 |
11 |
黑暗 |
$53.33 |
視訊遊戲 |
12 |
建立您自己的鍵盤 |
$77.95 |
模型與愛好 |
[訂單詳細數據] 資料表
詳細標識碼 |
訂單識別碼 |
產品名稱 |
產品識別碼 |
單價 |
數量 |
Discount |
---|---|---|---|---|---|---|
1 |
1 |
建立您自己的鍵盤 |
12 |
$77.95 |
9 |
5% |
2 |
1 |
非宏指令圖 |
2 |
$78.88 |
4 |
7.5% |
3 |
2 |
練習計算機格子! The DVD! |
7 |
$14.88 |
6 |
4% |
4 |
2 |
魔幻電腦晶片 |
4 |
$32.65 |
8 |
0 |
5 |
2 |
計算機極客與神話生物 |
6 |
$78.50 |
4 |
0 |
6 |
3 |
訪問! 遊戲! |
5 |
$22.95 |
5 |
15% |
7 |
4 |
程序設計師動作圖 |
1 |
$12.95 |
2 |
6% |
8 |
4 |
Ultimate 飛披薩 |
8 |
$36.75 |
8 |
4% |
9 |
5 |
外部 5.25 吋磁碟驅動器 (1/4 Scale) |
9 |
$65.00 |
4 |
10% |
10 |
6 |
關係資料庫圖 |
3 |
$22.50 |
12 |
6.5% |
11 |
7 |
黑暗 |
11 |
$53.33 |
6 |
8% |
12 |
7 |
關係資料庫圖 |
3 |
$22.50 |
4 |
9% |
下列步驟假設 [訂單] 數據表中的 [產品標識符] 字段與 [訂單詳細數據] 數據表之間的一對多關聯,且 [訂單] 數據表位於關聯的「一」端。
建立合計查詢
-
在 [建立] 索引標籤的 [查詢] 群組中,按一下 [查詢設計]。
-
選取您要處理的數據表,然後按下 [ 新增]。
每個資料表都會隨即在查詢設計師的上半部以視窗形式出現。
如果您使用先前列出的範例數據表,請新增 [產品] 和 [訂單詳細數據] 數據表。
-
按兩下您要用於查詢的數據表欄位。
原則上,您只會將群組字段和值欄位新增至查詢。 不過,您可以使用計算而非值字段,接下來的步驟會說明如何執行。
-
將 [產品] 資料表中的 [類別] 字段新增至設計網格線。
-
在網格線的第二欄中輸入下列表達式,以建立計算每個交易銷售金額的欄:
總銷售額: (1-[訂單詳細數據]。[Discount]/100) * ([訂單詳細數據]。[單價]*[訂單詳細數據]。[數量])
請確定您在表示式中參照的欄位為 [數位] 或 [貨幣] 資料類型。 如果您參照其他數據類型的字段,當您嘗試切換到 [數據工作表檢視] 時,Access 會在準則表示式中顯示不相符 的錯誤訊息。
-
在 [ 查詢設計] 索引 標籤的 [ 顯示/隱藏 ] 群組中,按兩下 [ 合計]。
[合計] 列會出現在設計網格線中,而在該列中,[群組依據] 會出現在第一欄和第二欄。
-
在第二欄中,將 [ 合計 ] 列中的值變更為 [加總]。 Sum 函數會新增個別銷售數位。
-
按兩下 [執行 ] 以執行查詢,並在 [資料工作表檢視] 中顯示結果。
-
請在下一節中保持查詢開啟以供使用。
搭配合計查詢使用準則
您在上一節中建立的查詢包含基礎表中的所有記錄。 它並不會在計算總計時排除任何順序,而且會顯示所有類別的總計。
如果您需要排除某些記錄,您可以在查詢中新增準則。 例如,您可以忽略低於 $100 美元的交易,或只計算部分產品類別的總金額。 本節中的步驟說明如何使用三種類型的準則:
-
計算合計時忽略特定群組的準則。 例如,您只會計算「視頻遊戲」、「藝術與框架」,以及「運動」類別的總計。
-
計算后隱藏特定總計的準則。 例如,您只能顯示大於 $150,000 的總計。
-
排除個別記錄不包含在總計中的準則。 例如,當 (單價 * 數量) 降到 $100 以下時,您可以排除個別銷售交易。
下列步驟說明如何逐一新增準則,並查看對查詢結果的影響。
新增準則至查詢
-
在 [設計檢視] 中開啟上一節的查詢。 若要這麼做,請以滑鼠右鍵按下查詢的檔索引標籤,然後按兩下 [ 設計檢視]。
-或-
在 [導航窗格] 中,以滑鼠右鍵按下查詢,然後按兩下 [ 設計檢視]。
-
在 [類別標識符] 欄的 [ 準則 ] 列中,輸入 =Dolls 或 Sports 或 Art and Framing。
-
按兩下 [執行 ] 以執行查詢,並在 [資料工作表檢視] 中顯示結果。
-
切換回 [設計視圖],然後在 [總銷售額] 欄的 [ 準則 ] 列中輸入 >100。
-
執行查詢以查看結果,然後切換回 [設計視圖]。
-
現在新增準則以排除低於 $100 的個別銷售交易。 若要這麼做,您必須新增另一欄。
附註: 您無法在 [總銷售額] 欄中指定第三個準則。 您在此欄中指定的任何準則都適用於總值,而非個別值。
-
將表達式從第二欄複製到第三欄。
-
在新欄的 [ 合計 ] 列中,選取 [ 位置 ],然後在 [ 準則 ] 列中 輸入 >20。
-
執行查詢以查看結果,然後儲存查詢。
附註: 下次在 [設計視圖] 中開啟查詢時,您可能會注意到設計網格線有微微變更。 在第二欄中, [欄位 ] 列中的運算式會以 Sum 函數 括住,而 [ 合計 ] 列中的值會顯示 [表達式 ] 而非 [加總]。
總銷售額:加總 ( (1-[訂單詳細數據]。折扣/100) * ([訂單詳細數據]。Unitprice*[Orde r Details]。數量) )
您也會看到第四欄。 此欄為第二欄的複本,但您在第二欄中指定的準則實際上會顯示為新欄的一部分。
-
使用交叉表查詢加總多個群組的數據
交叉表查詢是一種特殊類型的查詢,會以類似 Excel 工作表的網格線顯示結果。 交叉表查詢會摘要您的值,然後依兩組事實加以分組:一組在側邊 (一組列標題) ,另一組則放在頂端 (一組欄標題) 。 此圖說明交叉表查詢範例結果集的一部分:
繼續進行時請記住,交叉表查詢不一定會填入結果集中的所有欄位,因為您在查詢中使用的數據表不一定包含每個可能數據點的值。
當您建立交叉表查詢時,通常會包含來自多個數據表的數據,而且一律會包含三種數據類型:用來做為列名的數據、用來做為欄名的數據,以及您要加總或其他計算的值。
本節中的步驟假設下列數據表:
[訂單] 數據表
訂單日期 |
出貨日期 |
出貨城市 |
運送費用 |
---|---|---|---|
11/14/2005 |
11/15/2005 |
Jakarta |
$55.00 |
11/14/2005 |
11/15/2005 |
雪梨 |
$76.00 |
11/16/2005 |
11/17/2005 |
雪梨 |
$87.00 |
11/17/2005 |
11/18/2005 |
Jakarta |
$43.00 |
11/17/2005 |
11/18/2005 |
巴黎 |
$105.00 |
11/17/2005 |
11/18/2005 |
斯圖加特 |
$112.00 |
11/18/2005 |
11/19/2005 |
維也納 |
$215.00 |
11/19/2005 |
11/20/2005 |
邁阿密 |
$525.00 |
11/20/2005 |
11/21/2005 |
維也納 |
$198.00 |
11/20/2005 |
11/21/2005 |
巴黎 |
$187.00 |
11/21/2005 |
11/22/2005 |
雪梨 |
$81.00 |
11/23/2005 |
11/24/2005 |
Jakarta |
$92.00 |
[訂單詳細數據] 資料表
訂單識別碼 |
產品名稱 |
產品識別碼 |
單價 |
數量 |
Discount |
---|---|---|---|---|---|
1 |
建立您自己的鍵盤 |
12 |
$77.95 |
9 |
5% |
1 |
非宏指令圖 |
2 |
$78.88 |
4 |
7.5% |
2 |
練習計算機格子! The DVD! |
7 |
$14.88 |
6 |
4% |
2 |
魔幻電腦晶片 |
4 |
$32.65 |
8 |
0 |
2 |
計算機極客與神話生物 |
6 |
$78.50 |
4 |
0 |
3 |
訪問! 遊戲! |
5 |
$22.95 |
5 |
15% |
4 |
程序設計師動作圖 |
1 |
$12.95 |
2 |
6% |
4 |
Ultimate 飛披薩 |
8 |
$36.75 |
8 |
4% |
5 |
外部 5.25 吋磁碟驅動器 (1/4 Scale) |
9 |
$65.00 |
4 |
10% |
6 |
關係資料庫圖 |
3 |
$22.50 |
12 |
6.5% |
7 |
黑暗 |
11 |
$53.33 |
6 |
8% |
7 |
關係資料庫圖 |
3 |
$22.50 |
4 |
9% |
下列步驟說明如何建立交叉表查詢,將總銷售額依縣/市分組。 查詢使用兩個表達式來傳回格式化日期和銷售總額。
建立交叉資料表查詢
-
在 [建立] 索引標籤的 [查詢] 群組中,按一下 [查詢設計]。
-
按兩下要用於查詢的數據表。
每個資料表都會隨即在查詢設計師的上半部以視窗形式出現。
如果您使用範例數據表,請按兩下 [訂單] 資料表和 [訂單詳細資料] 資料表。
-
按兩下您要在查詢中使用的欄位。
每個功能變數名稱都會出現在設計網格線 [ 功能 變數] 列的空白儲存格中。
如果您使用範例數據表,請從 [訂單] 資料表新增 [出貨城市] 和 [出貨日期] 字段。
-
在 [ 欄位 ] 列中的下一個空白儲存格中,複製並貼上或輸入下列表達式: 總銷售額:總銷售額 (CCur ([訂單詳細數據]。[單價]*[數量]* (1-[Discount]) /100) *100)
-
在 [ 查詢設計] 索引 標籤的 [ 查詢類型] 群組中,按兩下 [ 交叉表]。
[ 合計 ] 列和 交叉表列 會出現在設計網格線中。
-
按兩下 [城市] 字段中 [ 合計 ] 列中的儲存格,然後選取 [群組依據]。 對 [出貨日期] 欄位執行相同動作。 將 [總銷售額] 字段 [ 合計 ] 儲存格中的值變更為 [表達式]。
-
在 交叉表列 中,將 [城市] 欄位中的儲存格設為[ 列名],將 [出貨日期] 字段設為 [欄名],並將 [總銷售額] 字段設為 [值]。
-
在 [ 查詢設計] 索引 標籤的 [ 結果 ] 群組中,按兩下 [ 執行]。
查詢結果會顯示在 [數據工作表檢視] 中。
匯總函數參照
此表格列出並說明 Access 在 [合計] 列和查詢中提供的匯總函數。 請記住,Access 為查詢提供的匯總函數,比對 [合計] 列提供更多的匯總函數。
函數 |
描述 |
使用 資料類型 () |
---|---|---|
Average |
計算某欄的平均值。 該欄必須包含數字、貨幣或日期/時間資料。 此函數會忽略 Null 值。 |
數位、貨幣、日期/時間 |
Count |
計算欄中的項目數量。 |
複雜的重複純量資料 (例如多重值清單欄) 以外的所有資料類型。 如需多重值清單的詳細資訊,請參閱建立或刪除多重值欄位一文。 |
Maximum |
傳回最高值的項目。 對於文字數據,最高值是最後一個字母值 -Access 會忽略大小寫。 此函數會忽略 Null 值。 |
數位、貨幣、日期/時間 |
Minimum |
傳回最低值的項目。 對於文字數據,最低值是第一個字母值 -Access 會忽略大小寫。 此函數會忽略 Null 值。 |
數位、貨幣、日期/時間 |
標準差 |
衡量值與平均值 (平均數) 之間的離散程度。 如需使用此函數的詳細資訊,請參閱 在數據工作表中顯示欄合計一文。 |
數位、貨幣 |
Sum |
加總欄中的項目。 僅適用於數字和貨幣資料。 |
數位、貨幣 |
Variance |
計算欄中所有值的統計變異數。 此函數僅適用於數字和貨幣資料。 如果數據表包含的數據列少於兩列,Access 會傳回 Null 值。 如需變異數函數的詳細資訊,請參閱 在數據工作表中顯示欄合計一文。 |
數位、貨幣 |