使用交叉資料表查詢讓摘要資料更容易閱讀

使用 [設計檢視] 建立交叉資料表查詢時，您可以視需要使用多個記錄來源 (資料表和查詢)。 不過，您可以保持使用簡單的設計，方法是先建立會傳回您想要的所有資料的選取查詢，然後使用該查詢做為交叉資料表查詢的記錄來源。 如需建立選取查詢的詳細資訊，請參閱另請參閱一節。

當您在 [設計檢視] 中建立交叉資料表查詢時，是使用設計格線中的 [合計] 和 [交叉資料表] 資料列來指定哪個欄位的值會變成欄名，哪些欄位的值會變成列名，以及會使用哪個欄位的值來計算總計、平均、計數或其他彙總值。

1. 這些資料列中的設定會決定欄位是列名、欄名或摘要值。

2. 這項設定會將欄位的值顯示為列名。

3. 這項設定會將欄位的值顯示為欄名。

4. 這些設定會產生摘要值。

建立查詢

1. 在 [建立] 索引標籤的 [查詢] 群組中，按一下 [查詢設計]。

   

2. 在 [顯示資料表] 對話方塊中，按兩下您要用來做為記錄來源的每一個資料表或查詢。

   如果您使用多個記錄來源，請確認這些資料表或查詢是以共通的欄位聯結在一起。 如需聯結資料表和查詢的詳細資訊，請參閱另請參閱一節。

3. 關閉 [顯示資料表] 對話方塊。

4. 在 [設計] 索引標籤上，按一下 [查詢類型] 群組中的 [交叉資料表]。

5. 在查詢設計視窗中，按兩下您要用來做為列名來源的每一個欄位。 您最多可以選取三個欄位做為列名來源。

6. 在查詢設計格線中，於每一個列名欄位的 [交叉資料表] 資料列中選取 [列名]。

   您可以在 [ 準則 ] 數據列中輸入表達式，以限制該欄位的結果。 您也可以使用 [ 排序 ] 列來指定欄位的排序順序。

7. 在查詢設計視窗中，按兩下您要做為欄名來源的欄位。 您只能選取一個欄位做為欄名。

8. 在查詢設計格線中，於欄名欄位的 [交叉資料表] 資料列中選取 [欄名]。

   您可以在 [ 準則 ] 列中輸入表達式，以限制欄標題字段的結果。 不過，搭配欄標題字段使用準則表達式並不會限制交叉表查詢傳回的數據行數目。 而是會限制哪些欄包含數據。 例如，假設您有一個欄標題字段，其中包含三個可能的值：紅色、綠色和藍色。 如果您將 準則 ='blue' 套用至欄名字段，交叉表仍會顯示紅色欄和綠色欄，但只有藍色欄包含數據。

   如果您想要限制顯示為欄名的值，可以使用查詢的 [欄名] 屬性來指定一份固定值清單。 如需詳細資訊，請參閱下一節。

9. 在查詢設計視窗中，按兩下您要用來計算摘要值的欄位。 您只能選取一個欄位用來做為摘要值。

10. 在查詢設計格線中，於摘要值欄位的 [合計] 資料列中選取要用來計算值的總合函數。

11. 在摘要值欄位的 [交叉資料表] 列中，選取 [值]。

    您無法指定摘要值欄位的準則或排序順序。

12. 在 [設計] 索引標籤上的 [結果] 群組中，按一下 [執行]。

指定固定值做為欄名

如果您想要指定固定值做為欄名，可以設定查詢的 [欄名] 屬性。

1. 在 [設計檢視] 中開啟交叉資料表查詢。

2. 如果沒有看見屬性表，請按 F4 鍵以顯示屬性表。

3. 在屬性表的 [一般] 索引標籤上方，確認 [選取類型] 是 [查詢屬性]。 如果不是，請按一下查詢設計格線上方空間中的空白點。

4. 在屬性表的 [一般] 索引標籤上，於 [欄名] 屬性中輸入以逗號分隔的值清單，這些值就是您要用來做為欄名的值。

   有些字元 (例如大部分的標點符號) 不能出現在欄名中。 如果您在值清單中使用這些字元，Access 會以底線 (_) 來取代每一個這種字元。

交叉資料表查詢的 SQL 語法

交叉資料表查詢在 SQL 中是以 TARNSFORM 陳述式的形式表示。 TRANSFORM 陳述式包含下列語法：

TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[， value2[， ...]]) ]

TRANSFORM 陳述式由下列各部分組成：

部分	描述
aggfunction	對所選資料執行的 SQL AGGREGATE 函數。
selectstatement	SELECT 陳述式。
pivotfield	您要用於在查詢的結果集中建立欄標題的欄位或運算式。
value1, value2	用於建立欄標題的固定值。

[SQL 檢視] 不會限制可用來做為交叉資料表查詢之記錄來源的資料表或查詢數量。 不過，您可以保持使用簡單的設計，方法是建立會傳回您想要用於交叉資料表查詢的所有資料的選取查詢，然後使用該查詢做為記錄來源。 如需建立選取查詢的詳細資訊，請參閱另請參閱一節。

1. 在 [建立] 索引標籤上，按一下 [其他] 群組中的 [查詢設計]。

2. 關閉 [顯示資料表] 對話方塊。

3. 在 [設計] 索引標籤的 [檢視] 群組中，按一下 [檢視]，再按一下 [SQL 檢視]。

4. 在 [SQL 物件] 索引標籤中，輸入或貼上以下 SQL：

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. 在第一行中的 TRANSFORM 後方，輸入要用來計算摘要值的運算式；例如 Sune([Amount])。

   如果您使用多個資料表或查詢做為記錄來源，請在每個欄位名稱中加上資料表或查詢名稱、例如 Sum([Expense].[Amount])。

6. 在第二行中的 SELECT 後方，輸入您要做為列名使用的欄位或欄位運算式清單。 請使用逗號分隔清單項目；例如 [Budget].[Dept_ID], [Expense].[Type]。

7. 在第三行中的 FROM 後方，輸入您做為記錄來源的資料表或查詢清單；例如 Budget, Expense。

8. 在第四行中的 GROUP BY 後方，輸入在步驟 6 中用於 SELECT 子句的相同欄位清單。

9. 在第五列中的 PIVOT 後方，輸入您要做為欄名使用的欄位名稱或運算式；例如 PIVOT [Budget].[Year]。

有時候，您可能想要將欄位的值組成數個範圍，然後使用這些範圍做為列名或欄名，而不是使用欄位的所有值做為列名或欄名。 例如，假設您要使用「年齡」欄位做為欄名。 您可能會偏好使用一欄來表示一個年齡範圍，而不是每一個年齡都使用一欄來表示。

您可以在運算式中使用 IIf 函數來建立範圍，以便用來做為列名或欄名。

1. 在 [設計檢視] 中開啟交叉資料表查詢。

2. 在查詢設計格線中，於 [欄位] 資料列中，以滑鼠右鍵按一下某空白資料行，然後按一下捷徑功能表上的 [顯示比例]。

3. 在 [放大顯示] 方塊中，輸入欄位別名，後面再加上冒號 (:)。

4. 輸入 IIf()。

5. 在 IIf 後面的刮號內輸入比較運算式，以定義欄位值的第一個範圍。

   例如，假設您正在建立 [年齡] 字段的範圍，而您希望每個範圍都為二十年。 第一個範圍的比較表達式是 [Age]<21。

6. 在比較運算式後面輸入逗號，然後輸入範圍的名稱，並用引號括住。 您所提供的名稱是落入該範圍的值的交叉資料表標題。

   例如，在 [年齡]<21 後面輸入逗號，再輸入 "0-20 歲"。

7. 在範圍名稱後面輸入一個逗號 (位於引號外面)，然後執行下列其中一項：

   • 若要建立另一個範圍，請輸入 IIf() 然後重複執行步驟 5、6 和 7。

   • 如果是最後一個範圍，只需輸入範圍名稱。

     例如，可將「年齡」欄位劃分成以二十年為範圍的完整巢狀 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 陳述式評估為 True，就會停止評估。 您不需要指定各個範圍的起點，因為任何值若落在某指定範圍的起點之下，早已經是評估為 True。

8. 在查詢設計格線中，選取 [合計] 資料列中的 [群組]。

9. 在 [交叉資料表] 資料列中，指定是要使用範圍做為列名或欄名。 請記得，您只能指定一至三個列名以及一個欄名。

您可能會想要讓交叉資料表查詢在執行時顯示要求輸入的提示訊息。 例如，假設您使用數個列名，其中一個是「國家/地區」。 此時您可能會想要讓查詢提示輸入某個名稱，然後根據使用者的輸入來顯示資料，而不是一律顯示所有國家或地區的資料。

您可以在任何列名欄位中加入參數提示。

1. 在 [設計檢視] 中開啟交叉資料表查詢。

2. 在您要提示使用者輸入資訊的列名欄位的 [準則] 資料列中，輸入以方括號括住的問題文字。 查詢執行時，問題文字就會顯示成提示訊息。

   例如，如果您在 [準則] 資料列中輸入 [哪一個國家或地區?]，當查詢執行時，就會出現一個對話方塊，其中含有「哪一個國家或地區?」這個問題的輸入方塊，以及一個 [確定] 按鈕。

   提示: 如果想要讓參數有彈性，可以使用 Like 運算子以萬用字元來串連運算式。 例如，也可以不使用 [哪一個國家或地區?] 做為準則，而使用 Like [哪一個國家或地區?]&"*" 讓參數符合更大的輸入範圍。 使用 Like 並不會變更參數提示的外觀。

3. 在 [設計] 索引標籤的 [顯示/隱藏] 群組中，按一下 [參數]。

4. 在 [ 查詢參數] 對話框的 [ 參數 ] 欄中，輸入您在 [ 準則 ] 列中使用的相同參數提示。 包含方括弧，但不包含任何串連通配符或 Like 運算符。

5. 在 [資料類型] 資料欄中，選取每個參數的資料類型。 此資料類型應該符合列名欄位的資料類型。

如果交叉資料表查詢中用來計算摘要值的欄位含有 Null 值，您使用的任何總合函數都會忽略這些值。 如果是使用某些總合函數，甚至可能會影響結果。 例如，若要計算平均值，您要加總所有的值，然後將加總結果除以值的個數。 但如果欄位含有任何 Null 值，這些 Null 值的數目並不會算入值的個數中。

在某些情況下，您可能會想要以零來取代任何 Null 值，這樣在進行總合計算時就會將這些值算進去。 您可以使用 Nz 函數以零來取代 Null 值。

Nz 語法

Nz ( variant [, valueifnull ] )

Nz 函數語法具有下列引數：

引數	描述
variant	必要。 Variant 資料類型的變數。
valueifnull	選用 (除非在查詢中使用)。 若 variant 引數為 Null 時，提供要傳回值的 Variant。 這個引數可讓您傳回零或零長度字串以外的值。 附註: 如果在查詢中使用運算式中的 Nz 函數，而不使用 valueifnull 引數，結果會是包含 Null 值之欄位中的零長度字串。

1. 在 [設計檢視] 中開啟查詢，於查詢設計格線中，以滑鼠右鍵按一下 [值] 欄位。

2. 按一下捷徑功能表上的 [顯示比例]。

3. 在 [放大顯示] 方塊中，以括號括住欄位名稱或運算式，然後在括號前面輸入 Nz。

4. 在右括號內直接輸入 , 0。

   例如，如果使用 Nz 與稱為「損失時數」的欄位將 Null 值轉換為零，完成的運算式看起來會如下：

Nz([Hours Lost], 0)

• 簡單為上    隨著列組合數的增加，交叉表查詢可能會變得難以閱讀。 列名的使用數目不會超過您需要的數目。

• 考慮在步驟中建立交叉表    請勿限制自己只使用數據表。 您通常可以先建立 合計查詢 ，然後使用該查詢做為交叉表查詢的記錄來源。

• 仔細選擇您的欄標題欄位    當欄標題的數目相對較小時，交叉表數據工作表往往更容易閱讀。 當您識別出要用來做為標題的欄位后，請考慮使用具有最少相異值的欄位來產生欄名。 例如，如果您的查詢是根據年齡和性別來計算值，請考慮使用性別做為欄名，而不是年齡，因為性別的可能值通常會比年齡小。

• 在 WHERE 子句中使用子查詢    您可以在交叉表查詢中使用子查詢做為 WHERE 子句的一部分。

頁面頂端