Accessクエリでは、データを集計する機能を持っています。SQL文ではレコードをグループ化してデータを集計しますが、この操作をクエリで自動的に行ってくれます。Access選択クエリとSQL文の関係 の記事で、選択クエリのSQL文を紹介しましたが、集計時にはどのような構文になるかも紹介したいと思います。
それでは、さっそく見ていきましょう。
データ準備
今回の説明では以下のサンプルデータを用います。
まず、サンプルテーブルを作成し、以下の設定をします。
表 サンプルテーブルの構造
| フィールド名 | データ型 |
| 品目 | 短いテキスト |
| 個数 | 数値型 |
作成したサンプルテーブルに以下のレコードを登録します。
表 サンプルデータ
| 品目 | 個数 |
| りんご | 5 |
| ぶどう | 10 |
| りんご | 1 |
| みかん | 19 |
| レモン | 16 |
| いちじく | 4 |
| メロン | 9 |
| レモン | 5 |
| みかん | 21 |
ここで、「りんご」や「レモン」は重複してレコードを登録していますが、それぞれの合計を算出するには、該当レコードをすべて抽出して計算する必要があります。このようなときに、Accessクエリの集計機能を用いることで、手間を省くことができます。
クエリの集計機能
サンプルテーブルが出来上がったら、次はサンプルクエリを作成し、「品目」と「個数」フィールドを【フィールド】項目に設定してください。そして、「デザイン」タブの「集計」アイコンをクリックします。すると、クエリ画面の下側に【集計】項目が追加され、各フィールドに対しては「グループ化」がセットされています。
ここで、「グループ化」とは同一の値をまとめる設定で、「品目」フィールドについては「りんご」や「レモン」をまとめることができますが、一方で「個数」フィールドでは別の値となっているため、まとめることができません（「りんご」のレコードで数量は「1」と「5」にしていますが、これらは別の値のため、まとめることができません。仮にそれぞれのレコードで「個数」フィールドの値が「1」と「1」であったり、「5」と「5」であれば、「品目」も「個数」フィールドも同じ値が登録されることになるため、レコードをまとめて出力することができます。）。このため、このままデータシートビューでレコードを確認すると、集計前と同様の結果が出力されます。
さて、集計機能では、数量の合計を算出したり、平均を算出したりと、数値型に対しての処理をすることが一般的です。「品目」フィールドでグループ化をし、「個数」の演算処理をしていきましょう。なお、図2には、【集計】項目に設定できる内容の一覧を示しています。
集計機能の設定と結果
まずは、「個数」フィールドの【集計】項目に「合計」を入れてみましょう。この場合、「品目」フィールドは同一の値で「グループ化」し、「個数」フィールドには品目毎の「合計」値が出力されることになります。例えば、「りんご」の場合は「１＋５＝６」の合計値が出力されています。
同様に、今度は【集計】項目に「平均」を入れてみましょう。この場合、「個数」フィールドには品目毎の「平均」値が出力されることになります。例えば、「りんご」の場合は「（１＋５）/２＝３」の平均値が出力されています。
この他にも、「最大値」、「最小値」の場合は、グループ内のレコード中で該当する値を出力する機能や、「カウント」のような該当するレコード数がいくつあるかを表示する機能もあります。
「りんご」を例にして、各設定をした場合の出力内容は次のようになります。
「りんご」をグループ化して集計した場合の各条件に対する結果
（「個数」フィールドには「１」と「５」の値が登録された２レコードが存在）
| 集計条件 | 出力結果 |
| 合計 | 6 |
| 平均 | 3 |
| 最小 | 1 |
| 最大 | 5 |
| カウント | 2 |
| 標準偏差 | 2.8284… |
| 分散 | 8 |
| 先頭 | 5（りんごが始めに現れるレコードの個数） |
| 最後 | 1（りんごが最後現れるレコードの個数） |
集計クエリのSQL文
さて、これまでクエリの集計機能を見てきましたが、Access内部ではどのようにSQL文を作成しているかを見てみましょう。
まず、先ほど作成したクエリの「個数」フィールドにおいて【集計】項目を「合計」としてください。次に、「デザイン」タブの「表示」アイコン中「SQLビュー」を選択します。これによりクエリ画面が切り替わり、SQL文が表示されます。今回の場合は、
SELECT [サンプルテーブル].品目, Sum([サンプルテーブル].個数) AS 個数の合計
FROM サンプルテーブル GROUP BY [サンプルテーブル].品目;
と記載されています。
ここで、SQL文における「GROUP BY フィールド名」は当該フィールドでグループ化することを意味しており、クエリの【集計】項目で「グループ化」を設定したフィールドに一致します。また、「Sum（フィールド名）」は、当該フィールドの値を合計した結果を返すことを意味しています。さらに、「AS ○○」は当該フィールドの名称を「○○」として出力することを意味しています。
このようにして、Access内でSQL文を自動作成し、図3に示す結果が出力されるようになります。
SQL文の理解が進むと、例えば「Sum」の部分を「Avg」にすることで平均値を返すこともでき、図4の結果を得ることができます。
まとめ
今回は、クエリの集計機能について紹介しました。対象とするフィールドのデータ型によって集計条件の設定方法を考える必要がありますが、あるデータ毎の合計値や平均値を容易に出力することができ、業務効率の改善が図れますので、是非操作方法を覚えていただければと思います。
スポンサーリンク
コメント