約1年ぶりにAccessクエリについての記事投稿を再開しました。
今回はクロス集計クエリの使い方を解説します。
もくじ
クロス集計クエリは、複数のテーブルやクエリを組み合わせて新しい集計表を作成できる、強力な機能です。
本記事では、実例を使いながらAccessクロス集計クエリの使い方と覚えておきたい設定方法を解説します。
クロス集計クエリを作る手順
Accessのクロス集計クエリを作る方法は、大きく2つの方法があります。
- クロス集計クエリウィザードを使う
- デザインビューから設定する
本記事では、後者のデザインビューを使った方法について解説していきます。
![デザインビューでクエリ作成](https://sys-daddy.com/wp-content/uploads/2023/06/make_cross_tab_query_01_01.jpg)
前者の方法については、クエリウィザードの解説記事でもクロス集計クエリの作り方を紹介しているので、そちらも参考にしてください。
手順1-デザインビューを開いてクエリを切り替える
クロス集計の元になるクエリは「Q02_クロス集計前」です。
このクエリには発注実績がまとめられています。
![発注集計がまとめられたクエリ](https://sys-daddy.com/wp-content/uploads/2023/04/make_cross_tab_query_02-1024x356.jpg)
次に、クロス集計したいテーブルやクエリを空のデザインビューに入れます。
![クロス集計したいテーブルやクエリ](https://sys-daddy.com/wp-content/uploads/2023/06/make_cross_tab_query_03_01.jpg)
クエリデザインタブの「クロス集計」をクリックし、選択クエリからクロス集計クエリに切り替えます。
![クロス集計クエリに切り替え](https://sys-daddy.com/wp-content/uploads/2023/06/make_cross_tab_query_04_01.jpg)
すると、デザイングリッドの項目が変わりました。
![デザイングリッドの項目が変わる](https://sys-daddy.com/wp-content/uploads/2023/06/make_cross_tab_query_05_01.jpg)
手順2-見出しと値を設定する
次に、選んだフィールドの見出しと値を設定します。
集計欄には、グループ化だけでなく、合計や平均などさまざまな集計方法を設定できます。
![集計方法を選択](https://sys-daddy.com/wp-content/uploads/2023/06/make_cross_tab_query_06_01.jpg)
クロス集計で設定できる集計方法は、集計クエリと基本的に同じです。
集計クエリの使い方については、Accessクエリシリーズの4回目の記事で詳しく解説しています。
行列の入れ替えでは、選んだフィールドの行見出し、列見出し、値を選択します。
行列見出しの選択は、クロス集計表の配置を決めるための重要な設定です。
![クロス集計表の配置を決める](https://sys-daddy.com/wp-content/uploads/2023/06/make_cross_tab_query_07_01.jpg)
見出し列では、グループ化以外の集計方法を選んだ場合、エラーで結果を表示できません。
したがって、見出し列の集計方法は必ずグループ化を設定しておきましょう。
![見出し列はグループ化を選択](https://sys-daddy.com/wp-content/uploads/2023/06/make_cross_tab_query_09_01.jpg)
本記事では、下の画像のようにクエリを設定しました。
![作ったクロス集計クエリ](https://sys-daddy.com/wp-content/uploads/2023/04/make_cross_tab_query_10.jpg)
結果を表示させると、発注先ごとの金額が月別に集計されました。
![クロス集計クエリの実行結果](https://sys-daddy.com/wp-content/uploads/2023/04/make_cross_tab_query_11.jpg)
クロス集計クエリは下のようなアイコンです。
アイコンは選択クエリとは少し異なるので、再編集の時に見分けやすいです。
![クロス集計クエリのアイコン](https://sys-daddy.com/wp-content/uploads/2023/06/make_cross_tab_query_14_01.jpg)
また、見出しや集計方法の設定が正しくないと、にエラーが表示されて先に進めません。
![エラーが表示される](https://sys-daddy.com/wp-content/uploads/2023/04/make_cross_tab_query_08.jpg)
もしエラーが表示された場合は、もう一度設定を確認しましょう。
重要-列見出しの項目を設定する
クロス集計クエリを使いこなすためは、クエリ列見出しの設定方法を知っておくことも重要です。
例えば、先ほど作ったクロス集計クエリには5月分の見出しが含まれていません。
しかし、5月分の元データが存在しない場合でも、見出しとして表示したい場合もあります。
![5月の見出しが無い](https://sys-daddy.com/wp-content/uploads/2023/06/set_column_name_04_01.jpg)
では、5月の見出しを作ってみましょう。
列見出しを右クリックしてプロパティをクリックします。
![プロパティを開く](https://sys-daddy.com/wp-content/uploads/2023/06/set_column_name_01_01.jpg)
プロパティの中に「クエリ列見出し」という設定が見つかります。
デフォルトでは空欄になっていますが、表示させたい項目を記入できます。
![クエリ列見出し](https://sys-daddy.com/wp-content/uploads/2023/04/set_column_name_02.jpg)
5月分の項目名も含めて月名を順番に記入します。
![月名を順番に入力](https://sys-daddy.com/wp-content/uploads/2023/06/set_column_name_03_01.jpg)
データシートビューを表示させると、5月分の項目が作成されました。
![5月の見出しが作られた](https://sys-daddy.com/wp-content/uploads/2023/06/set_column_name_04_01-1.jpg)
さらに、SQL文も確認してみましょう。
プロパティで入力した内容がSQL文にも反映されています。
![SQL文のクエリ列見出し](https://sys-daddy.com/wp-content/uploads/2023/06/set_column_name_06_01.jpg)
VBAコードを使ってクロス集計クエリを自動編集したい時は、デザインビューから作成したSQL文を転用すると便利です。
応用-2つ以上のテーブルからクロス集計クエリを作る
クロス集計クエリは1つのクエリやテーブルだけでなく、複数からも作成することができます。
さっそく、2つのテーブルからクロス集計クエリを作ってみましょう。
元となるテーブル間の連結は忘れずに設定しておきます。
![テーブル間を連結](https://sys-daddy.com/wp-content/uploads/2023/06/make_from_multi_table_01_01.jpg)
デザイングリッドで年月はFormat関数、金額は掛け算を設定しました。
![クエリに関数を設定](https://sys-daddy.com/wp-content/uploads/2023/06/make_from_multi_table_02_01.jpg)
クエリの結果を確認したところ、予想どおりのクロス集計クエリが作成されました。
![完成したクロス集計クエリ](https://sys-daddy.com/wp-content/uploads/2023/04/make_cross_tab_query_11-1.jpg)
2つのテーブルを連結させてクロス集計クエリを作る方法を使うと、Accessデータベースの中にムダなクエリを作らずに済みます。
おまけ‐Excelピボットテーブルとの違い
Excelのピボットテーブルもデータのクロス集計を行う機能で、Accessのクロス集計クエリと同じような構成を持つ表を作ることができます。
Accessのクロス集計クエリとExcelのピボットテーブルの仕上がりの違いについても少しだけ触れていきます。
Accessのクロス集計クエリの場合は、整列された表ができ上ります。
ただし、見出しを設置する項目数などに制限があるので、やや自由度が少ないです。
![クロス集計クロスは整列された表](https://sys-daddy.com/wp-content/uploads/2023/06/differnce_to_pivottable_01_01.jpg)
一方、Excelのピボットテーブルは折りたためる表が仕上がります。
制限なく項目を見出しに設定できるので、とても自由度が高いです。
![ピボットテーブルは折りたためる表](https://sys-daddy.com/wp-content/uploads/2023/06/differnce_to_pivottable_02_01.jpg)
どんな表を作り上げるかという目的に合わせて、両者をうまく使い分けるとよいでしょう。
まとめ
今回はAccessのクロス集計クエリの作り方を解説しました。
クエリウィザードでクロス集計クエリを作ることはできますが、デザインビューを使えば細かい調整も可能です。
また、Excelのピボットテーブルとの違いもしっかりと把握しておくと、データ加工や集計がより便利になるでしょう。