クロス集計クエリを使用して集計データを読みやすくする

デザイン ビューを使用してクロス集計クエリを作成すると、必要な数のレコード ソース (テーブルとクエリ) を使用できます。 ただし、最初に必要なすべてのデータを返す選択クエリを作成し、そのクエリをクロス集計クエリの唯一のレコード ソースとして使用することで、設計をシンプルに保つことができます。 選択クエリの作成方法については、「関連項目」セクションを参照してください。

デザイン ビューでクロス集計クエリを作成するときは、デザイン グリッドの [集計] 行および [クロス集計] 行を使用して、値を列見出しに使用するフィールド、値を行見出しに使用するフィールド、および合計、平均、カウント、または他の計算の対象フィールドを指定します。

1. これらの行の設定により、フィールドが行見出し、列見出し、または集計値のいずれになるかが決まります。

2. この設定では、フィールドの値が行見出しとして表示されます。

3. この設定では、フィールドの値が列見出しとして表示されます。

4. これらの設定により、集計値が作成されます。

クエリを作成する

1. [作成] タブの [クエリ] グループで [クエリ デザイン] をクリックします。

   

2. [テーブルの表示] ダイアログ ボックスで、レコード ソースとして使用するそれぞれのテーブルまたはクエリをダブルクリックします。

   複数のレコード ソースを使用する場合は、テーブルまたはクエリが共通するフィールドで結合されていることを確認します。 テーブルとクエリを結合する方法の詳細については、[参照] セクションを参照してください。

3. [テーブルの表示] ダイアログ ボックスを閉じます。

4. [デザイン] タブの [クエリの種類] で [クロス集計] をクリックします。

5. クエリ デザイン ウィンドウで、行見出しのソースとして使用する各フィールドをダブルクリックします。 行見出しにはフィールドを 3 つまで選択できます。

6. クエリ デザイン グリッドの、それぞれの行見出しフィールドに対する [クロス集計] 行で [行見出し] をクリックします。

   [抽出条件] 行に式を入力して、フィールドの結果を制限することができます。 [並べ替え] 行を使用して、フィールドの並べ替え順序を指定することもできます。

7. クエリ デザイン ウィンドウで、列見出しのソースとして使用するフィールドをダブルクリックします。 列見出しには、1 つのフィールドのみを選択できます。

8. クエリ デザイン グリッドの、列見出しフィールドに対する [クロス集計] 行で [列見出し] をクリックします。

   [抽出条件] 行に式を入力して、列見出しフィールドの結果を制限することができます。 ただし、列見出しフィールドに条件式を使用しても、クロス集計クエリから返される列の数は制限されません。 代わりに、データが含まれる列が制限されます。 たとえば、赤、緑、および青の 3 つの値を使用できる列見出しフィールドがあるとします。 列見出しフィールドに条件 ='青' を適用した場合、クロス集計に赤の列と緑の列は表示されますが、データは青の列にのみ格納されます。

   列見出しとして表示する値を制限する場合は、クエリの [列見出し] プロパティを使用して、固定値のリストを指定することができます。 詳細については、次のセクションを参照してください。

9. クエリ デザイン ウィンドウで、集計値の計算に使用するフィールドをダブルクリックします。 集計値に使用するフィールドを 1 つだけ選択することができます。

10. クエリ デザイン グリッドの、集計値に対する [集計] 行で、値の計算に使用する集計関数を選択します。

11. 集計値フィールドに対する [クロス集計] 行で、[値] をクリックします。

    集計値フィールドには、条件または並べ替えを指定できません。

12. [デザイン] タブの [結果] グループで、[実行] をクリックします。

列見出しに固定値を指定する

列見出しに使用する固定値を指定する場合は、クエリの "列見出し" プロパティを設定します。

1. デザイン ビューでクロス集計クエリを開きます。

2. プロパティ シートが表示されていない場合は、F4 キーを押して表示します。

3. プロパティ シートで、[標準] タブのすぐ上の [選択の種類] が [クエリ プロパティ] になっていることを確認します。 そうでない場合は、クエリ デザイン グリッドの上のスペースで空白の場所をクリックします。

4. プロパティ シートの [標準] タブの "列見出し" プロパティで、列見出しとして使用するカンマ区切りの値のリストを入力します。

   一部の文字 (ほとんどの句読点など) は、列見出しに使用できません。 これらの文字を値のリストに使用すると、該当する文字がそれぞれアンダースコア (_) に置き換えられます。

クロス集計クエリの SQL 構文

クロス集計クエリは、SQL では TRANSFORM ステートメントとして表されます。 TRANSFORM ステートメントの構文は、次のとおりです。

TRANSFORM aggfunction
selectstatement
PIVOT ピボットフィールド [IN (value1[, value2[, ...]])]

TRANSFORM ステートメントでは、次の引数を使用します。

引数	説明
aggfunction	選択されたデータに対して実行される SQL 集計関数。
selectstatement	SELECT ステートメント。
pivotfield	クエリの結果セットの列見出しの作成に使用するフィールドまたは式。
value1, value2	列見出しの作成に使用する固定値。

SQL ビューでは、クロス集計クエリのレコード ソースとして使用できるテーブルまたはクエリの数は制限されません。 ただし、クロス集計クエリで使用するすべてのデータを返す選択クエリを作成し、その選択クエリをレコード ソースとして使用することで、設計をシンプルに保つことができます。 選択クエリの作成方法については、「関連項目」セクションを参照してください。

1. [作成] タブの [その他] グループで、[クエリ デザイン] をクリックします。

2. [テーブルの表示] ダイアログ ボックスを閉じます。

3. [デザイン] タブの [ビュー] グループで、[ビュー] をクリックし、[SQL ビュー] をクリックします。

4. [SQL オブジェクト] タブで、次の SQL 構文を入力するか貼り付けます。

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. 1 行目の TRANSFORM の後に、集計値の計算に使用する式を入力します (例: Sum([Amount]))。

   複数のテーブルまたはクエリをレコード ソースとして使用している場合は、各フィールド名にテーブル名またはクエリ名を含めます (例: Sum([Expense].[Amount]))。

6. 2 行目の SELECT の後に、行見出しに使用するフィールドまたはフィールド式のリストを入力します。 複数のリスト項目は、コンマで区切ります (例: [Budget].[Dept_ID], [Expense].[Type])。

7. 3 行目の FROM の後に、レコード ソースとして使用するテーブルまたはクエリのリストを入力します (例: Budget, Expense)。

8. 4 行目の GROUP BY の後に、手順 6 の SELECT 句で使用したのと同じフィールド リストを入力します。

9. 5 行目の PIVOT の後に、列見出しに使用するフィールド名または式を入力します (例: PIVOT [Budget].[Year])。

行見出しまたは列見出しのそれぞれの値を使う代わりに、フィールド値を範囲でグループ化して、その範囲を行見出しまたは列見出しで使用する場合があります。 たとえば、列見出しに "年齢" フィールドを使用するとします。 そのような場合は、それぞれの年齢の列を使用するよりも、年齢層を表す列を使用する方が適しています。

式で IIf 関数を使用することにより、行または列の見出しに使用する範囲を作成できます。

1. デザイン ビューでクロス集計クエリを開きます。

2. クエリ デザイン グリッドの [フィールド] 行で、空白の列を右クリックし、ショートカット メニューの [ズーム] をクリックします。

3. [ズーム] ボックスに、フィールドの別名と、その後にコロン (:) を入力します。

4. 「IIf()」と入力します。

5. IIf の後に続くかっこの中に、フィールド値の 1 つ目の範囲を定義する比較式を入力します。

   たとえば、"年齢" フィールドの範囲を作成していて、各範囲を 20 歳にするとします。 最初の範囲の比較式は [年齢]<21 です。

6. 比較式の後にカンマを入力し、範囲の名前を引用符で囲んで入力します。 指定する名前は、その範囲にある値のクロス集計の見出しになります。

   たとえば、[年齢]<21 の後にカンマを入力し、次に「"0 ～ 20 歳"」と入力します。

7. 範囲名の後 (引用符の外側) にカンマを入力して、次のいずれかの操作を行います。

   • 別の範囲を作成するには、「IIf()」と入力して、手順 5、6、7 を繰り返します。

   • 最後の範囲には、範囲の名前のみを入力します。

     たとえば、"年齢" フィールドを 20 歳の範囲に区切る、完全に入れ子になった 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"))))
     

     注: 式の評価は、IIf ステートメントのいずれかが True と評価されると、すぐに中止されます。 それぞれの範囲の下限を指定する必要はありません、指定された範囲を下回る値はすべて True と評価されるためです。

8. クエリ デザイン グリッドの [集計] 行で [グループ化] をクリックします。

9. [クロス集計] 行で、範囲を行見出しまたは列見出しとして使用するかどうかを指定します。 行見出しは 1 つから 3 つ、列見出しは 1 つ指定することができます。

クロス集計クエリの実行時に入力を求めるようにしたい場合があります。 たとえば、複数の行見出しを使用していて、その 1 つが [国/地域] であるとします。 その場合、すべての国または地域のデータを毎回表示するよりも、クエリで名前の入力を求め、ユーザーの入力に従ってデータを表示することができます。

任意の行見出しフィールドにパラメーター プロンプトを追加することができます。

1. デザイン ビューでクロス集計クエリを開きます。

2. ユーザーの入力を求める行見出しフィールドの [抽出条件] 行に、質問文を角かっこで囲んで入力します。 質問文はクエリの実行時にプロンプトとして表示されます。

   たとえば、[抽出条件] 行に、「[国または地域を入力してください。]」と入力すると、クエリが実行されたときにダイアログ ボックスが表示され、その中に "国または地域を入力してください。" という質問、入力ボックス、および [OK] ボタンが表示されます。

   ヒント: パラメーターを柔軟にする場合は、 Like 演算子を使用して、式をワイルドカードで連結します。 たとえば、[ どの国 または地域ですか?] を 基準として使用する代わりに、[ どの国または地域か] のように使用できます。&"*" を使用して、パラメーターをより広い入力範囲に一致させます。 Like を使用しても、パラメーター プロンプトの外観は変更されません。

3. [デザイン] タブの [表示/非表示] で [パラメーター] をクリックします。

4. [クエリ パラメーター] ダイアログ ボックスの [パラメーター] 列に、[抽出条件] 行で使用したのと同じパラメーター プロンプトを入力します。 角かっこも含めますが、連結したワイルドカードや Like 演算子は含めないでください。

5. [データ型] 列で、各パラメーターのデータ型を選択します。 このデータ型は、行見出しフィールドのデータ型と一致する必要があります。

クロス集計クエリの集計値を計算するために使用するフィールドに NULL 値が含まれている場合、その値は使用するすべての集計関数で無視されます。 一部の集計関数では、結果に影響が出る可能性があります。 たとえば、平均を計算するには、すべての値を加算し、その結果を値の数で除算します。 ただし、フィールドに NULL 値が含まれている場合、その NULL 値は、値の数にカウントされません。

場合によっては、集計計算で値がカウントされるように、すべての NULL 値をゼロに置き換える方が望ましいこともあります。 NULL 値をゼロに置き換えるには、Nz 関数を使用します。

Nz 構文

Nz ( variant [, valueifnull ] )

Nz 関数の構文には、次の引数があります。

引数	説明
variant	必須。 データ型 Variant の変数です。
valueifnull	オプション (クエリで使用されている場合を除く)。 variant 引数が NULL の場合に返す値を提供する Variant です。 この引数を使用すると、0 または長さが 0 の文字列以外の値を返すことができます。 注: 引数 valueifnull を使用せずに、クエリの式で Nz 関数を使用すると、NULL 値を含むフィールドは長さが 0 の文字列になります。

1. デザイン ビューでクエリを開いた状態で、クエリ デザイン グリッドの "値" フィールドを右クリックします。

2. ショートカット メニューの [ズーム] をクリックします。

3. [ズーム] ボックスで、フィールド名または式をかっこで囲み、かっこの前に「Nz」と入力します。

4. 右かっこの直前に「, 0」と入力します。

   たとえば、Nz と "失われた時間" という名前のフィールドを使用して NULL 値をゼロに変換する場合、入力する式は次のようになります。

Nz([Hours Lost], 0)

• 簡潔に記述する    クロス集計クエリは、行の組み合わせ数が増えると読みにくくなる可能性があります。 必要以上に行見出しを使用しないでください。

• 手順を追ってクロス集計を作成することを検討する    テーブルのみを使おうとして考えを限定しないでください。 集計クエリを作成し、そのクエリをクロス集計クエリのレコード ソースとして使用して開始することもできます。

• 列見出しフィールドは慎重に選択する    クロス集計データシートは、列見出しの数が比較的少ない方が読みやすくなります。 見出しとして使用するフィールドを確認したら、列見出しとして作成される値が最も少ないフィールドを選択します。 たとえば、クエリで年齢と性別による値を計算する場合、通常、性別の方が年齢よりも値が少ないため、年齢ではなく性別を列見出しに使用します。

• WHERE 句でサブクエリを使用する    クロス集計クエリでは、WHERE 句の一部としてサブクエリを使用できます。

ページの先頭へ