本記事では Access の 選択クエリ操作で 集計-グループ クエリ、集計-合計 クエリを使ってテーブルのフィールド値を集計する方法を紹介します。
目次
Access の選択クエリで 集計-グループ 集計- 合計 クエリを使ってみよう
本記事では
の中で紹介した
- T0000_東京都コロナ発症状況_マスタ テーブル からお互いに同じものがない日付の作成が必要
- 日付毎に入院数と退院数を合計することが必要
で作成した
T1020_日付_退院者フラグ_入院者フラグ テーブル
をもとに､ Access の選択クエリのなかで 集計-グループ、集計- 合計 クエリを使ってこれらの実装を行う手順について詳しく紹介します。
具体的には記事 #18 で紹介済の
M1000_作成_T1040_日付_退院者数_入院者数_退院率
の中の
Q1030_作成_T1030_日付_退院者数_入院者数
つまり
についての詳細な紹介になります。
選択クエリ対象のテーブル仕様
入力テーブル
このクエリの入力となる
T1020_日付_退院者フラグ_入院者フラグ
は以下のようなものです。
全体を把握するために先頭部分と最後尾の２つのデータシートビューをのせておきます｡
このテーブルをみると
- 退院者フラグ、入院者フラグは両方とも数値型
- 退院者フラグのレコードは最後尾以前で欠損している
という特徴があります。
出力テーブル
そしてこちらが
Q1030_作成_T1030_日付_退院者数_入院者数
を実行後の出力テーブル
T1030_日付_退院者数_入院者数
です｡
こちらも全体を把握するために先頭部分と最後尾の２つのデータシートビューをのせておきます｡
どうでしょう
- 全てのレコードは日付毎の退院者数の合計、入院者数の合計になっている
- 退院者の合計のレコードは最後尾以前で欠損している
という感じでしょうか。
さてこの
Q1030_作成_T1030_日付_退院者数_入院者数
はどうやって退院者数と入院者数を集計するのでしょうか。
テーブルの集計方法を確認しよう
テーブル集計の具体的な手順は動画を見た方が早いので
次の動画再生画面を一度クリックして下に三本線メニュを出し 1 グループ化と合計計算 を選択してから何回かご覧ください。
画面をフルスクリーンにしたり､巻き戻したり､必要に応じて音楽をミュート後､再生速度を変えたりして何回か見て頂くことをお勧めします。
|
いかがでしたか？
動画の 2 フィールド間の計算 以降のチャプタは
次の記事 #22 で詳しく紹介しますのでお楽しみに。
それでは今回のテーマに沿って
テーブルの集計方法を紹介しますね。
集計クエリの使い方
それでは改めて
Q1030_作成_T1030_日付_退院者数_入院者数
を デザインビュー で確認しましょう｡
何？ これ。
また変なことになってるんですけど～
デザインビュー の下の部分をデザイン グリッド といいますが
このデザイン グリッド に今までなかった 集計行 が追加されてますね～
これってどこから来たんでしょうね？
今まであまり明確に紹介していませんでしたが 、Access の レコード は以下の Excel の セル のように個々のデータに明確なアドレスがないので、以下のように縦方向の レコード にまたがる計算ができません｡
そのかわり テーブル の レコード をグループ化しながらいろいろな集計計算をすることができます。
え～ダメじゃん
それってどゆこと？
私のことは嫌いになってもいいから
Access のことは嫌いにならないで～(笑)
次の画面は
Q1030_作成_T1030_日付_退院者数_入院者数
を作り始めるに当たって最初に
T1020_日付_退院者フラグ_入院者フラグ
からフィールドを選択する画面です｡
その結果、データシートビュー は以下のようになりますが
ここからデザインビューに切り替えると次のようになりますよね｡
ここでリボンビューの中央にある集計(Σ)ボタンを押すとデザイン グリッドに通常表示されない 集計行 が現れます｡
これで 集計クエリ の準備ができました｡
グループ化とは
この 集計行 の各フィールドに設定されている グループ化 はどういう意味でしょうか。
これはこの テーブルの中にある全てのレコードを上から順に参照し､同じ値のレコード値をグループ化､つまり一つにまとめるという意味になります｡
これはどういうことかと言うと
例えば、この入力テーブル
T1020_日付_退院者フラグ_入院者フラグ
の 日付 フィールドの値は
2020/06/19
2020/06/19
2020/06/19
2020/06/19
：
2020/06/19
2020/06/19
2020/06/20
2020/06/20
：
のように同じものが繰り返され、しばらくすると違うものが出てきますよね。
これを
2020/06/19
2020/06/20
：
のようにまとめる｡
そういう意味です｡
グループ毎の合計とは
続いて次の2枚の画像にでてくる グループ毎の 合計 を見てみましょう｡
先ほど見た 集計 欄では プルダウンメニュー からいろいろな種類の集計操作を選ぶことができますが
合計 はこのテーブルの中にある全てのレコードを上から順に参照し
同じ グループ（つまり同じ 日付 をもった レコード）の フィールド 値（ここでは退院者フラグと入院者フラグ）の 合計 を グループ毎に集計します｡
これは Excel で ピボットテーブル 作成後に 集計 するのと同じようなイメージですね
結果としてこのクエリは最終的に以下のようになります。
そしてこのクエリの実行結果を以下のようにデータシートビューで確認すると
以下のような結果になります(動画の中で合計値のフィールド名は退院者数、入院者数に変更しています)。
どうでしょう。
動画の中では､このクエリが期待どうりの結果になったので､このクエリを元にいつものようにテーブル作成クエリにしています。
テーブル作成クエリの詳細手順は必要に応じて
を参照してくださいね｡
グループ集計行で使える演算
ここからは参考情報です｡
デザインビューのデザイングリッドに集計行を追加したとき､上で見たようないろいろな計算ができますが､ここではよく使いそうな集計行の項目、それに対応する集計関数をまとめておきます｡
| 集計行 | 対応する集計関数 | 意味 |
|---|---|---|
| グループ化 | ー | グループ内に含まれる列内の項目をグループに分類します |
| 合計 | Sum | グループ内に含まれる列内の項目を加算します。 |
| 平均 | Average | グループ内に含まれる列内の平均値を計算します。 |
| 最小 | Minimum | グループ内に含まれる列内最も小さい値のアイテムを返します。 |
| 最大 | Maximum | グループ内に含まれる列内最大の値を持つアイテムを返します |
| カウント | Count | グループ内に含まれる列内のアイテム数をカウントします。 |
| 標準偏差 | Standard Deviation | グループ内に含まれる列内統計的な対象となる値がその平均からどれだけ広い範囲に分布しているかを計測します。 |
| 分散 | Variance | グループ内に含まれる列内列内のすべての値の統計的変位を計測します。 |
| 先頭 | ー | グループ内に含まれる列内の先頭のアイテムを返します。 |
| 最後 | ー | グループ内に含まれる列内最後尾のアイテムを返します。 |
この集計関数の詳細は マイクロソフト の公式サイト の 集計関数リファレンス で確認してくださいね｡
まとめ
いかがですか？
今回は Access の 選択クエリの中にある集計クエリの使い方として
- グループ化のやり方
- グループ毎の合計の求め方
- 集計クエリで利用可能なその他集計項目
について紹介しました。
次回は今回求めた
T1030_日付_退院者数_入院者数
からレコード毎の計算を行い､グラフ作成の元テーブルとなる
T1040_日付_退院者数_入院者数_退院率
の作成方法
M1000_作成_T1040_日付_退院者数_入院者数_退院率
マクロの作成方法等について紹介しましょう。
さてこの記事全体の目標は
- 今の状況から抜け出してもっと前向きな仕事に取り組みたい
- さっさと仕事を切り上げて充実したアフターファイブを過ごしたい
でしたよね。
あなたの解決したい問題は何ですか？
次回はまた
で Access VBA の新しい窓を開きましょう。
Microsoft Access 関連教材は次のリンクからどうぞ。
(有償)本記事中で参照している 16_01.accdb から個人のPC環境に依存する部分を削除した 16_02.zip のダウンロードは次のリンクからどうぞ。
いつも記事を読んで頂きありがとうございます～
今日の調子はいかがですか？