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