使用交叉表查詢讓匯總數(shù)據(jù)更容易閱讀

Access > 查詢

 

使用交叉表查詢讓匯總數(shù)據(jù)更容易閱讀

全部顯示

全部隱藏

如果您希望重構匯總數(shù)據(jù)，使其更容易閱讀和理解，請考慮使用交叉表查詢。

交叉表查詢計算總和、平均值或其他聚合函數(shù) （聚合函數(shù)：用于計算總計的函數(shù)。例如：SUM、COUNT、AVG 或 VAR。），然后按照兩組值對結(jié)果進行分組：一組值垂直分布在數(shù)據(jù)表的一側(cè)，而另一組值水平分布在數(shù)據(jù)表的頂端。

 注釋    不可在 Web 瀏覽器中使用交叉表查詢。如果要在 Web 數(shù)據(jù)庫中運行交叉表查詢，必須先使用 Access 打開數(shù)據(jù)庫。

本文內(nèi)容

---

概述

創(chuàng)建交叉表查詢

對標題使用范圍或間隔

提示輸入用于限制行標題的參數(shù)

將空值替換為零

交叉表查詢提示

---

概述

交叉表查詢是一種選擇查詢 （選擇查詢：就表中存儲的數(shù)據(jù)提出問題，然后在不更改數(shù)據(jù)的情況下以數(shù)據(jù)表的形式返回一個結(jié)果集。）。在運行交叉表查詢時，結(jié)果顯示在一個數(shù)據(jù)表中，該數(shù)據(jù)表的結(jié)構不同于其他類型的數(shù)據(jù)表。

與顯示相同數(shù)據(jù)的簡單選擇查詢（如下圖所示）相比，交叉表查詢的結(jié)構讓數(shù)據(jù)更易于閱讀。

---

此選擇查詢按員工和類別在垂直方向?qū)R總數(shù)據(jù)進行分組。

交叉表查詢可以顯示相同的數(shù)據(jù)，但它同時在水平方向和垂直方向?qū)?shù)據(jù)進行分組，這樣數(shù)據(jù)表可以更緊湊并且更容易閱讀。

---

創(chuàng)建交叉表查詢

在創(chuàng)建交叉表查詢時，需要指定哪些字段包含行標題，哪些字段包含列標題以及哪些字段包含要匯總的值。在指定列標題和要匯總的值時，其中每個只能使用一個字段。在指定行標題時，最多可使用三個字段。

提示  此外，也可以使用表達式生成行標題、列標題或要匯總的值。

---

位于此側(cè)的一列、兩列或三列包含行標題。用作行標題的字段名稱顯示在這些列的最上面的一行中。

此處顯示行標題。在使用多個行標題字段時，由于將顯示所有行標題組合，因此交叉表數(shù)據(jù)表中的行數(shù)會迅速增加。

位于此側(cè)的列包含列標題和匯總值。請注意，列標題字段的名稱不顯示在數(shù)據(jù)表中。

此處顯示匯總值。

---

創(chuàng)建交叉表查詢的方法

使用交叉表查詢向?qū)?/span>  交叉表查詢向?qū)ǔＪ莿?chuàng)建交叉表查詢的最快、最簡單的方法。該向?qū)槟瓿纱蟛糠值墓ぷ?，但有些選項它沒有提供。

該向?qū)Ь哂邢铝袃?yōu)點：

提示  在對列標題使用“日期/時間”字段中的值時，如果要將日期組合為該向?qū)Р惶峁┑拈g隔（如財政年度或兩年期間），則不要使用該向?qū)?chuàng)建查詢，而應該在設計視圖中創(chuàng)建交叉表查詢，然后使用表達式創(chuàng)建這些間隔。

但不能使用該向?qū)?zhí)行下列任務：

 注釋    在向?qū)У淖詈笠徊?，可以選擇在設計視圖中修改查詢。這樣可以添加向?qū)Р恢С值牟樵冊O計元素，如附加記錄源。

在設計視圖中工作   通過設計視圖，可以對查詢設計進行更多的控制。該視圖支持向?qū)е形刺峁┑囊恍┕δ堋?/span>

如果您要執(zhí)行下列任務，請考慮使用設計視圖創(chuàng)建交叉表查詢：

在 SQL 視圖中編寫查詢  如果愿意，您可以在 SQL 視圖中編寫交叉表查詢，但不能使用 SQL 視圖指定參數(shù)數(shù)據(jù)類型。如果要在交叉表查詢中使用參數(shù)，必須通過在設計視圖中修改查詢來指定參數(shù)數(shù)據(jù)類型。

提示  請記住，不必局限于一種方法來創(chuàng)建交叉表查詢?？梢允褂孟?qū)?chuàng)建查詢，然后再使用設計視圖修改查詢設計。

返回頁首

創(chuàng)建交叉表查詢

使用交叉表查詢向?qū)?chuàng)建交叉表查詢

在設計視圖中創(chuàng)建交叉表查詢

在 SQL 視圖中創(chuàng)建交叉表查詢

---

使用交叉表查詢向?qū)?chuàng)建交叉表查詢

如果使用交叉表查詢向?qū)В瑒t需要將單個表或查詢用作交叉表查詢的記錄源。如果單個表中不具有您要包含在交叉表查詢中的全部數(shù)據(jù)，則首先應創(chuàng)建一個返回所需數(shù)據(jù)的選擇查詢。有關創(chuàng)建選擇查詢的詳細信息，請參閱請參閱部分。

將啟動交叉表查詢向?qū)А?/span>

最多可選擇三個字段用作行標題源，但使用的行標題越少，交叉表查詢數(shù)據(jù)表就越容易閱讀。

 注釋    如果選擇多個字段來提供行標題，則選擇這些字段的順序?qū)Q定對結(jié)果排序的默認順序。

通常應選擇一個包含很少值的字段，這樣有助于使結(jié)果易于閱讀。例如，最好是使用只包含少量可能值（如性別）的字段，而不是使用包含許多不同值（如年齡）的字段。

如果選擇用于列標題的字段具有“日期/時間”數(shù)據(jù)類型，則向?qū)黾右粋€步驟，使您能夠指定將日期組合為間隔（如月份或季度）的方式。

如果包含行小計，則交叉表查詢中有一個附加行標題，該標題與字段值使用相同的字段和函數(shù)。包含行小計還會插入一個對其余列進行匯總的附加列。例如，如果交叉表查詢按位置和性別（使用性別列標題）計算平均年齡，該附加列會按位置計算平均年齡，而不分性別。

 注釋    可以通過在設計視圖中編輯交叉表查詢，更改用于生成行小計的函數(shù)。

返回頁首

在設計視圖中創(chuàng)建交叉表查詢

通過使用設計視圖創(chuàng)建交叉表查詢，可以根據(jù)需要使用任意多個記錄源（表和查詢）。不過，您可以讓設計簡單些，方法是：先創(chuàng)建一個返回所需的全部數(shù)據(jù)的選擇查詢，然后將該查詢用作交叉表查詢的唯一記錄源。有關創(chuàng)建選擇查詢的詳細信息，請參閱請參閱部分。

當在設計視圖中生成交叉表查詢時，使用設計網(wǎng)格中的“總計”和“交叉表”行指定哪個字段的值將成為列標題，哪些字段的值將成為行標題，哪個字段的值將用于計算總計、平均值、計數(shù)或其他計算。

---

這些行中的設置決定字段是行標題、列標題還是匯總值。

此設置將字段值顯示為行標題。

此設置將字段值顯示為列標題。

這些設置生成匯總值。

---

創(chuàng)建查詢

如果使用多個記錄源，請確保這些表或查詢基于它們共同的字段相聯(lián)接 （聯(lián)接：表格或查詢中的字段與另一表格或查詢中具有同一數(shù)據(jù)類型的字段之間的關聯(lián)。聯(lián)接向程序說明了數(shù)據(jù)之間的關聯(lián)方式。根據(jù)聯(lián)接的類型，不匹配的記錄可能被包括在內(nèi)，也可能被排除在外。）。有關聯(lián)接表和查詢的詳細信息，請參閱請參閱部分。

可以在“條件”行中輸入一個表達式來限制該字段的結(jié)果。還可以使用“排序”行指定字段的排序順序。

可以在“條件”行中輸入一個表達式來限制列標題字段的結(jié)果。但是，對列標題字段使用條件表達式不會限制交叉表查詢返回的列數(shù)，而是會限制哪些列中包含數(shù)據(jù)。例如，假設有一個列標題字段包含三個可能值：red、green 和 blue。如果將條件 ='blue' 應用于該列標題字段，則交叉表仍會顯示 red 列和 green 列，但只有 blue 列包含數(shù)據(jù)。

 注釋    如果要限制顯示為列標題的值，可以通過使用查詢的“列標題”屬性指定固定值的列表。有關詳細信息，請參閱為列標題指定固定值。

不能為匯總值字段指定條件，也不能在該字段上進行排序。

為列標題指定固定值

如果要指定用于列標題的固定值，可以設置查詢的“列標題”屬性。

 注釋    有些字符（如大多數(shù)標點符號）在列標題中是不允許使用的。如果在值列表中使用這些字符，則 Access 會將所有此類字符替換為下劃線 (_)。

返回頁首

在 SQL 視圖中創(chuàng)建交叉表查詢

交叉表查詢的 SQL 語法

交叉表查詢在 SQL 中表示為 TRANSFORM 語句。TRANSFORM 語句的語法如下：

TRANSFORM aggfunction

selectstatement

PIVOT pivotfield [IN (value1[, value2[, ...]])]

TRANSFORM 語句包含以下三部分：

---

SQL 視圖不限制可用作交叉表查詢的記錄源的表或查詢的數(shù)量。不過，您可以讓設計簡單些，方法是：創(chuàng)建一個返回要在交叉表查詢中使用的全部數(shù)據(jù)的選擇查詢，然后將該選擇查詢用作記錄源。有關創(chuàng)建選擇查詢的詳細信息，請參閱請參閱部分。

TRANSFORM

SELECT

FROM

GROUP BY

PIVOT

;

 注釋    如果要將多個表或查詢用作記錄源，請將表或查詢的名稱包含在各個字段名中；例如，Sum([Expense].[Amount])。

向行標題字段添加排序順序

若要在 SQL 視圖中向交叉表查詢添加排序順序，請使用 ORDER BY 子句。

默認情況下，ORDER BY 子句按升序?qū)χ颠M行排序。如果要按降序排序，請在字段名或表達式后面鍵入 DESC。

限制用于行或列標題的值

可以使用下列過程指定要用作列標題的值列表，并向行標題字段中添加條件。這些過程假定交叉表查詢已在 SQL 視圖中打開。

指定要用作列標題的固定值

 注釋    如果指定的固定值與數(shù)據(jù)透視字段中的字段值不對應，則該固定值將成為空列的列標題。

添加用于限制行標題的查詢條件

如果要使用附加條件，可以使用 AND 和 OR 運算符擴展 WHERE 子句。此外，還可以使用括號將條件組合為邏輯集。

返回頁首

對標題使用范圍或間隔

有時，需要將字段值組合為范圍，然后將這些范圍用于行或列標題，而不是將各個字段值用于行或列標題。例如，假設您對列標題使用“年齡”字段。您可能更愿意使用表示年齡范圍的列，而不是對每個年齡分別使用一列。

可以在表達式中使用 IIf 函數(shù)來創(chuàng)建要用于行或列標題的范圍。

提示  如果要使用“日期/時間”字段創(chuàng)建間隔，請考慮使用交叉表查詢向?qū)?。使用該向?qū)Э梢詫⑷掌诮M合為以下間隔：“年”、“季度”、“月”、“日期”或“日期/時間”。如果這些間隔都不能滿足您的要求，則應在設計視圖中創(chuàng)建交叉表查詢，然后使用此部分中介紹的方法創(chuàng)建所需的間隔。

IIf 的工作方式

IIf 的工作方式是：對一個表達式進行計算；如果該表達式為 true，則返回一個值；如果該表達式為 false，則返回一個替代值。通過嵌套 IIf 語句，可以創(chuàng)建一個邏輯比較序列。因此，可以使用 IIf 將數(shù)值字段值分隔為范圍。

IIF 語法

IIf(expr, truepart, falsepart)

IIf 函數(shù)語法包含以下這些參數(shù) （參數(shù)：為操作、事件、方法、屬性、函數(shù)或過程提供信息的值。）：

---

使用表達式創(chuàng)建范圍

例如，假設您要為“年齡”字段創(chuàng)建范圍，并希望將每個范圍設為二十年。則第一個范圍的比較表達式為 [Age]<21。

例如，在 [Age]<21 后面鍵入一個逗號，然后鍵入 "0-20 years"。

例如，以下所示是一個完整的嵌套 IIf 表達式（為易于閱讀，添加了換行符），該表達式將“年齡”字段分隔為包含二十年的范圍：

IIf([Age]<21,"0-20 years",

 IIf([Age]<41,"21-40 years",

 IIf([Age]<61,"41-60 years",

 IIf([Age]<81,"61-80 years", "80+ years"))))

 注釋    當 Access 對該表達式進行計算時，只要某一 IIf 語句的計算結(jié)果為 true，它就會停止計算。您不必指定每個范圍的下限，因為低于給定范圍下限的所有值的計算結(jié)果都為 true。

返回頁首

提示輸入用于限制行標題的參數(shù)

您可能希望交叉表查詢在運行時提示輸入數(shù)據(jù)。例如，假設要使用多個行標題，其中一個行標題為“國家/地區(qū)”。您可能希望查詢提示輸入一個名稱，然后根據(jù)用戶的輸入顯示數(shù)據(jù)，而不是為每個國家或地區(qū)顯示數(shù)據(jù)。

可以向任意行標題字段添加參數(shù)提示。

 注釋    也可以向列標題字段添加參數(shù)提示，但這不會限制所顯示的列。有關對所顯示的列進行限制的詳細信息，請參閱為列標題指定固定值部分。

例如，如果在“條件”行中鍵入 [Which country or region?]，則在運行查詢時，會出現(xiàn)一個對話框，其中包含問題“Which country or region?”、一個輸入框以及一個“確定”按鈕。

提示  如果希望能夠靈活地輸入?yún)?shù)，請使用 Like 運算符將表達式與通配符連接起來。例如，可以使用 Like [Which country or region?]&"*" 使參數(shù)與更大的輸入范圍相匹配，而不是將 [Which country or region?] 用作條件。使用 Like 不會更改參數(shù)提示的外觀。

返回頁首

將空值替換為零

如果在交叉表查詢中用于計算匯總值的字段包含空值，則所使用的任意聚合函數(shù)都會忽略這些值。對于某些聚合函數(shù)，這可能會影響到結(jié)果。例如，若要計算平均值，應將所有值相加并將相加結(jié)果除以這些值的數(shù)量。但如果字段包含任意空值，則這些空值將不會被計入值數(shù)量。

在有些情況下，您可能希望將所有空值都替換為零，以便在執(zhí)行聚合計算時會計入這些值?？梢允褂?Nz 函數(shù)將空值替換為零。

NZ 語法

Nz(variant [, valueifnull ] )

Nz 函數(shù)語法接受以下參數(shù)：

---

例如，如果將 Nz 用于名為“Hours Lost”的字段，以將 null 值轉(zhuǎn)換為零，那么最終的表達式將如下所示：

Nz([Hours Lost], 0)

返回頁首

交叉表查詢提示

返回頁首