本記事では 記事#26 でご紹介したバッチファイル
- %USERPROFILE%\Tools\bat\作成_東京都コロナウイルス_発症者推移グラフ.bat
から呼び出され、結合クエリ を使用して２つのテーブルを結合し、以下の 東京都コロナウイルス 男女別 入院者数 推移 グラフ
を描画するための元データ
- %USERPROFILE%\Tools_Data_Temp\xlsx\T2040_日付_女性入院者数_男性入院者数.xlsx
を作成するツール
- %USERPROFILE%\Tools\accdb\作成_T2040_日付_女性入院者数_男性入院者数_xlsx.accdb
の詳細についてご紹介します。
グラフ描画そのものは先ほどご紹介したバッチファイルの中で
この描画用の元データをグラフ描画用テンプレートに
転記することで行っています｡
このバッチファイルに興味のある方は
記事#26 も確認してくださいね｡
目次
グラフ描画に向けた考え方
まずこのグラフ描画用のデータの入った Excel ブックを見ておきましょう。
Excelをお使いの方ならこの Excel ブックから
先ほどの折れ線グラフをどう描画するかは
ご存じかもしれませんね～
それではこの連載の中で度々ご紹介している以下の T0000_東京都コロナ発症状況_マスタ テーブル からどうやってこの Excel ブックを作るかについて考えてみましょう。
この T0000_東京都コロナ発症状況_マスタ テーブル に含まれるデータは次の記事内のクエリ適用により既に 最新日付を含んだ400日分のデータとなっています。
テーブル内のレコードの特徴
さて先ほどのグラフを描画しようと思ったとき T0000_東京都コロナ発症状況_マスタ テーブル を眺めると以下の特徴が見てとれます。
- これらの レコード は特定日付に男女どちらか入院 したことを表すレコードである
- 特定日付に入院したのが男女どちらなのか､あるいはそれぞれ何人いるのかはあくまでその時々の状況によって決まる
- 従ってこれらのレコードに含まれる日付は必ずしも連続とは限らない
グラフ用データをどう作るか
この特徴を考慮すると T0000_東京都コロナ発症状況_マスタ テーブルからグラフ描画の為のデータ作成方針は次のようなものになるでしょうか。
- 日付は全レコードに含まれる日付をグループ化して作成
- 男性の日付毎の入院者数は日付をグループ化し男性入院数を合計して作成
- 男性の日付毎の入院者数は日付をグループ化し男性入院数を合計して作成
- これらを一つのグラフ用データとしてまとめるために 日付 をキーにして 女性入院数合計 と 男性入院数合計 を結合し日付､ 女性入院数合計､男性入院数合計 を フィールド をもつレコード をもった テーブル を作成
- このテーブル を Excel ブックとしてエクスポート
この最初の3項目については
で紹介した､集計クエリのグループ化、合計のテクニックを使って
- T0000_東京都コロナ発症状況_マスタ.公表年月日 フールドに対する 集計-グループ クエリを使うことで、T0000_東京都コロナ発症状況_マスタ 内に含まれる全日付を抽出したテーブルを作成する
- T0000_東京都コロナ発症状況_マスタ.患者_性別 = “男性” の条件に合致したレコードを対象にT0000_東京都コロナ発症状況_マスタ.入院者 の値を合計 クエリを使うことで、T0000_東京都コロナ発症状況_マスタ 内に含まれる 日付 毎の 男性の入院者数合計を抽出したテーブルを作成する
- T0000_東京都コロナ発症状況_マスタ.患者_性別 = “女性” の条件に合致したレコードを対象にT0000_東京都コロナ発症状況_マスタ.入院者 の値を合計 クエリを使うことで、T0000_東京都コロナ発症状況_マスタ 内に含まれる 日付 毎の 女性の入院者数合計を抽出したテーブルを作成する
ことができそうですね｡
なのでここは後ほど出来上がったクエリをさらっと紹介します。
また最後の テーブル を Excel ブックとしてエクスポートする方法についても同様に後ほどさらっと紹介します。
結合クエリを使いグラフ作成用データを作ろう
さてこの記事のメインテーマとなっている 4番目 の
- 日付 をキーにして 女性入院数合計 と 男性入院数合計 を結合し 日付､女性入院数合計､男性入院数合計 を フィールド をもつレコード をもった テーブル を作成
はどのように行うのでしょうか｡
動画で結合クエリの作り方を確認しよう
この具体的な手順は動画を見たほうがわかり易いと思いますので､まず次の動画を何回かご覧ください。
画面をフルスクリーンにしたり、再生スピードを変更したりして何回か見て頂くことをお勧めします。
結合クエリのデザインビュー
さて動画はいかがだったでしょうか｡
以下はこの動画の中で作成された結合クエリ Q2040_作成T2040日付女性入院者数男性入院者数 の最終的なデザインビューです。
このデザインビュー上部のリボンビューを見ると クエリの種類 の テーブルの作成 ボタンがハイライトされているので、このクエリは新しいテーブルを作成するアクションクエリです。
では具体的にどんな名前のテーブルを作成しているのかを確認するためにデザインビュー上部の何もないところでマウスの右ボタンクリック後、表示されるコンテキストメニューからテーブル作成を選ぶと
以下のテーブルの作成 ダイアログが現れるので、ここからこの Q2040_作成T2040日付女性入院者数男性入院者数 というクエリが
T2040_日付_女性入院者数_男性入院者数 というテーブルを作成することがわかります。
つまりこれらのことをまとめると､この結合クエリは (以降､ 〇〇 テーブル内の ◇◇ フィールドを 〇〇.◇◇ と表記します)
- T2000_日付 テーブル.日付 を T2040_日付_女性入院者数_男性入院者数.日付 として出力
- T2000_日付 テーブル.日付 = T2020_日付_男性_入院者数.公表_年月日 のとき T2020_日付_男性入院者数 .男性入院者数 を出力
- T2000_日付 テーブル.日付 = T2030_日付_女性_入院者数.公表_年月日 のとき T2030_日付_女性_入院者数 .女性入院者数 を出力
という仕事をするアクションクエリでもあるということです。
結合クエリに関連した入出力テーブル
それではここでさらに理解を深めるためにこの結合クエリ
- Q2040_作成_T2040_日付_女性入院者数_男性入院者数
の参照テーブル、出力テーブルをデータシートビューでみてみましょう。
T2000_日付 テーブル
まず以下がこの結合クエリが参照している T2000_日付 テーブル です。
この例では T2000_日付 テーブル .日付 のレコードに #2021/03/06#、#2021/03/07# と連続した日付データが格納されています( 以降 ##で囲んだデータは日付データと表記します )｡
あ、作成日の違いで収容されているレコードの日付が
動画と少し違っています。ごめんなさい。
T2020_日付男性入院者数
続いて以下もこの結合クエリが参照している T2020_日付男性入院者数 テーブルです。
このテーブルには
- 公表_年月日
- 患者_性別
- 男性入院者数
というフィールドが含まれており、こちらにも T2020_日付_男性入院者数 .公表_年月日 のレコードに #2021/03/06#、#2021/03/07# と連続した日付が格納されています。
T2030_日付_女性_入院者数
同様に以下もこの結合クエリが参照している T2030_日付_女性_入院者数 テーブルです。
このテーブルにも
- 公表_年月日
- 患者_性別
- 女性入院者数
というフィールドが含まれており、こちらにも T2020_日付_男性入院者数 .公表_年月日 のレコードに #2021/03/06#、#2021/03/07# と連続した日付が格納されています。
dd
T2040_日付_女性入院者数_男性入院者数
最後にこの結合クエリが出力する T2040_日付_女性入院者数_男性入院者数 テーブルを紹介します。
このテーブルはこの結合クエリの実行結果として
- 日付
- 男性入院者
- 女性入院者
という３つのフィールドが作成されています。
あ､この図では日付が降順になっていませんね｡
Access って明確に昇順/降順を指定をしないと
レコードの順番が期待どうりにならないので注意が必要です。
結合クエリ内テーブル間のリレーションシップ
さてここでこの結合クエリに関連したこれらのテーブルの先頭部分に注目し、作成された T2040_日付_女性入院者数_男性入院者数 テーブルに含まれるフィールドとその元になる以下の入力テーブル
- T2000_日付
- T2020_日付_男性_入院者数
- T2030_日付_女性_入院者数
に含まれるフィールド間の参照関係(リレーションシップ)を図示してみましょう。
どうでしょう｡
この参照関係(リレーションシップ)から T2040_日付_女性入院者数_男性入院者数 テーブルを構成するフィールドが
- T2000_日付 テーブル. 日付
- T2020_日付_男性入院者数 .男性入院者数 ( 但し T2000_日付 テーブル. 日付 = T2020_日付_男性入院者数 .公表_年月日 を満たすとき)
- T2030_日付_女性入院者数 .女性入院者数 ( 但し T2000_日付 テーブル. 日付 = T2030_日付_女性入院者数 .公表_年月日 を満たすとき)
を結合した形で T2040_日付_女性入院者数_男性入院者数 テーブルを作られていることが確認できますね｡
テーブル間にこのようなリレーションシップを定義できるデータベースは
RDB(Relational Data Base：リレーショナルデータベース)
と呼ばれます。
結合プロパティの意味
このことを確認するため【動画中で実施していないパターンも含め】マウスのドラッグ位置、ドロップ位置でリレーションシップがどう定義されるかについて網羅的にまとめて以下のような表にまとめてみました｡
この表の中では対象テーブルを T2000_日付 テーブルと T2020_日付男性入院者数 テーブルの２つのみにしぼりこの間の関係(リレーションシップ)が理解しやすいように
- クエリのデザインレビュー内のテーブルの見え方(結合リンクの矢印の有無、向き)
- 結合プロパティの設定値
- 結果として内部的に生成されるSQL文
をセットにしてまとめています｡
| マウスを左から右にドラッグ | マウスを右から左にドラッグ | |
|---|---|---|
|
1: |
 |
 |
 |
 |
|
| SELECT T2000_日付.[日付], T2020_日付_男性_入院者数.男性入院者数 FROM T2000_日付 INNER JOIN T2020_日付_男性_入院者数 ON T2000_日付.日付 = T2020_日付_男性_入院者数.公表_年月日; |
SELECT T2000_日付.日付, T2020_日付_男性_入院者数.男性入院者数 FROM T2020_日付_男性_入院者数 INNER JOIN T2000_日付 ON T2020_日付_男性_入院者数.公表_年月日 = T2000_日付.日付; |
|
|
2: |
 |
 |
 |
 |
|
| SELECT T2000_日付.日付, T2020_日付_男性_入院者数.男性入院者数 FROM T2000_日付 LEFT JOIN T2020_日付_男性_入院者数 ON T2000_日付.日付 = T2020_日付_男性_入院者数.公表_年月日; |
SELECT T2000_日付.日付, T2020_日付_男性_入院者数.男性入院者数 FROM T2020_日付_男性_入院者数 LEFT JOIN T2000_日付 ON T2020_日付_男性_入院者数.公表_年月日 = T2000_日付.日付; |
|
| 3: 右外部結合 |
 |
 |
 |
 |
|
| SELECT T2000_日付.日付, T2020_日付_男性_入院者数.男性入院者数 FROM T2000_日付 RIGHT JOIN T2020_日付_男性_入院者数 ON T2000_日付.日付 = T2020_日付_男性_入院者数.公表_年月日; |
SELECT T2000_日付.日付, T2020_日付_男性_入院者数.男性入院者数 FROM T2020_日付_男性_入院者数 RIGHT JOIN T2000_日付 ON T2020_日付_男性_入院者数.公表_年月日 = T2000_日付.日付; |
この表はマウスのドラッグ開始位置とドロップ位置で結合プロパティ画面内の
- 左のテーブル名(L)およびその下の左の列見出し(C)
- 右のテーブル名(R)およびその下の右の列見出し(C)
に何が設定されているか示しており､例えばこのテーブル内の大きな6区画の左上の区画は
マウスを右側の T2020_日付男性入院者数 テーブルの 公表_年月日 フィールド上でドラッグし、そのまま左側の T2000_日付 テーブルの 日付 フィールド上でドロップし、結合プロパティ画面内で一番上の 〇 をチェックし OK ボタンをクリックしたとき､これらのテーブルが内部結合として結合されること､Access 内部でどのようなSQLが生成されるかを表しています｡
SQLについては
それだけで一冊の本が書けるほど奥が深いので今は
｢ふーんそんなものがあるんだ｣位でいいですよ～。
テーブル間をマウスでドラッグ&ドロップして結合種別を指定するだけで
リレーションを含む SQL が簡単に作成できるなんて便利でしょ。
この表に当てはめると動画中では マウスを右から左にドラッグしたとき の 3: 番目の操作を行っていましたので､これは 左外部結合つまり T2020_日付男性入院者数 テーブルから見て T2000_日付 に含まれる全てのレコードを対象として結合していると言うことですね｡
あの～すいません
テーブルの左右ってどうやって決めてるんですかぁ？
これ自分もよくわからなかったので
先ほどの表を作ったんです
この 結合プロパティ ダイアログ の中でのテーブルの左右って何を基準にどう決めているのかが直感的にはわかりにくいのですが、結論としては結合プロパティに出てくるテーブルの左右で判断するのがよさそうです。
つまりこの表からもわかるように
- 最初にマウスのドラッグを始めたテーブル名が 左のテーブル名(L) に設定され、その時のフィールド名が 左の見出し(C) に設定され
- 最後にマウスをドロップしたテーブル名が 右のテーブル名(R) に設定され、その時のフィールド名を 右の見出し(C) に設定され
そしてこの設定パターンをベースにしてこの結合クエリの種別
- 内部結合(INNER JOIN)
- 左外部結合(LEFT JOIN)
- 右外部結合(RIGHT JOIN)
が定義されているという訳です。
内部/左外部/右外部 結合クエリとは
デザインビュー内の２つのテーブル間を結ぶ線の形状を見ると
－：内部結合(INNER JOIN)
→：左外部結合(RIGHT JOIN)
←：右外部結合(LEFT JOIN)
となっています｡
但しこの右左と､デザインビュー上で見えている
テーブルの配置には関係なく
あくまで結合プロパティ内の配置に依存しています。
ツール内マクロの概要
ここからはこのツール内のマクロの概要について紹介しますので興味のある方は以後の記事もご覧ください。
autoexec
autoexec はこのツール
- %USERPROFILE%\Tools\accdb\作成_T2040_日付_女性入院者数_男性入院者数_xlsx.accdb
の実行全体を制御するマクロです｡
このツール起動されると autoexec 内に記述されたマクロ
- M0000_Call_LinkExternalDbTable
- M2000_作成_T2040_日付_女性入院者数_男性入院者数
- M2010_Call_ExportTableToBook
を順に自動実行し､最後にこのAccessを終了します｡
M0000_Call_LinkExternalDbTable
M0000_Call_LinkExternalDbTable は
autoexec マクロから起動されるマクロで
このマクロ内でしらかば堂のライブラリ関数 LinkExternalDbTable を使用して
- %USERPROFILE%\Tools\accdb\作成_T0000東京都コロナ発症状況マスタ.accdb
内の
- T0000東京都コロナ発症状況_マスタ テーブル
に対するリンク情報を設定します｡
このマクロの詳細については必要に応じて以下の記事も参照ください。
M2000_作成_T2040_日付_女性入院者数_男性入院者数
M2000_作成_T2040_日付_女性入院者数_男性入院者数 は
autoexec マクロから起動され、このツールの中心をなすマクロです。
以下のように
- Q2000_作成_T2000_日付
- Q2020_作成_T2020_日付_男性_入院者数
- Q2030_作成_T2030_日付_女性_入院者数
- Q2040_作成_T2040_日付_女性入院者数_男性入院者数
を順番に実行します。
M2010_Call_ExportTableToBook
M2010_Call_ExportTableToBook は autoexec マクロから起動され
M2000_作成T2040日付女性入院者数男性入院者数
が作成した
- T2040_日付_女性入院者数_男性入院者数 テーブル
を
- %USERPROFILE%\Tools_Data_Temp\xlsx\T2040_日付_女性入院者数_男性入院者数.xlsx
ヘエクスポートします。
このマクロの詳細については必要に応じて以下の記事もご覧ください。
ツール内クエリの概要
ここからはこのツール内のクエリの概要について紹介しますので興味のある方は以後の記事もご覧ください。
Q2000_作成_T2000_日付
Q2000_作成_T2000_日付 は M2000_作成_T2040_日付_女性入院者数_男性入院者数 から起動されるクエリで
T0000_東京都コロナ発症状況_マスタ テーブル内に含まれる 公表_年月日 フィールドをグループ化し、グラフの横軸となる T2000_日付 テーブルを作成します。
このレコードをグループ化するクエリの作成方法については必要に応じて以下の記事もご覧ください。
理解を一層深めるための参考情報としてこのクエリのSQLもご紹介しておきます。
SELECT T0000_東京都コロナ発症状況_マスタ.公表_年月日 AS 日付
INTO T2000_日付
FROM T0000_東京都コロナ発症状況_マスタ
GROUP BY T0000_東京都コロナ発症状況_マスタ.公表_年月日;
Q2020_作成_T2020_日付_男性入院者数
Q2020_作成_T2020_日付_男性入院者数 は M2000_作成_T2040_日付_女性入院者数_男性入院者数 から起動されるクエリで
T0000_東京都コロナ発症状況_マスタ テーブル内のレコードで 患者_性別 が 男性 のレコードのみを対象に る 公表_年月日 フィールドと 患者_性別 フィールドをグループ化し、入院者フラグ フィールドの値を合計し グラフの縦軸となる T2020_日付_男性入院者数 テーブルを作成します。
このレコードをグループ化､合計するクエリの作成方法についても必要に応じ 記事#21 を参照ください｡
理解を一層深めるための参考情報としてこのクエリのSQLもご紹介しておきます。
SELECT T0000_東京都コロナ発症状況_マスタ.公表_年月日, T0000_東京都コロナ発症状況_マスタ.患者_性別, Sum(T0000_東京都コロナ発症状況_マスタ.入院者フラグ) AS 男性入院者数
INTO T2020_日付_男性_入院者数
FROM T0000_東京都コロナ発症状況_マスタ
GROUP BY T0000_東京都コロナ発症状況_マスタ.公表_年月日, T0000_東京都コロナ発症状況_マスタ.患者_性別
HAVING (((T0000_東京都コロナ発症状況_マスタ.患者_性別)="男性"));
Q2030_作成_T2030_日付_女性入院者数
Q2030_作成_T2030_日付_女性入院者数 は M2000_作成_T2040_日付_女性入院者数_男性入院者数 から起動されるクエリで
T0000_東京都コロナ発症状況_マスタ テーブル内のレコードで 患者_性別 が 女性 のレコードのみを対象に る 公表_年月日 フィールドと 患者_性別 フィールドをグループ化し、入院者フラグ フィールドの値を合計し グラフの縦軸となる T2030_日付_男性入院者数 テーブルを作成します。
このレコードをグループ化､合計するクエリの作成方法について興味のある方は 記事#21 を参照ください｡
理解を一層深めるための参考情報としてこのクエリのSQLもご紹介しておきます。
SELECT T0000_東京都コロナ発症状況_マスタ.公表_年月日, T0000_東京都コロナ発症状況_マスタ.患者_性別, Sum(T0000_東京都コロナ発症状況_マスタ.入院者フラグ) AS 女性入院者数
INTO T2030_日付_女性_入院者数
FROM T0000_東京都コロナ発症状況_マスタ
GROUP BY T0000_東京都コロナ発症状況_マスタ.公表_年月日, T0000_東京都コロナ発症状況_マスタ.患者_性別
HAVING (((T0000_東京都コロナ発症状況_マスタ.患者_性別)="女性"));
Q2040_作成_T2040_日付_女性入院者数_男性入院者数
Q2040_作成_T2040_日付_女性入院者数_男性入院者数 は M2000_作成_T2040_日付_女性入院者数_男性入院者数 から起動されるクエリで、この記事の主題となっているクエリです。内容についてはこの記事の冒頭部分でご紹介済です。
理解を一層深めるための参考情報としてこのクエリのSQLもご紹介しておきます。
SELECT T2000_日付.日付, T2020_日付_男性_入院者数.男性入院者数, T2030_日付_女性_入院者数.女性入院者数
INTO T2040_日付_女性入院者数_男性入院者数
FROM T2030_日付_女性_入院者数
RIGHT JOIN
(T2020_日付_男性_入院者数 RIGHT JOIN T2000_日付 ON T2020_日付_男性_入院者数.公表_年月日 = T2000_日付.日付)
ON T2030_日付_女性_入院者数.公表_年月日 = T2000_日付.日付;
まとめ
今回は 結合クエリで２つのテーブルを結合し、東京都コロナウイルス 男女別 入院者数 推移 グラフ の描画用の元データを作成するツールについて以下のポイントを中心に紹介しました。
- グラフ用データ作成に向けての考え方
- 結合クエリの作り方
- テーブル間のリレーションシップ
- 結合プロパティの意味
- 内部/左外部/右外部 結合クエリ
次の記事では Access で SQLとVBAを使ったクエリ操作とテーブル間リレーション操作により 東京都コロナウイルス世代別入院者数推移 グラフ
を描画するための元データを作成する
- 作成_T3200_日付_年代別入院者数_xlsx.accdb
の作成方法 について具体的に紹介します。
さてこの記事全体の目標は
- 今の状況から抜け出してもっと前向きな仕事に取り組みたい
- さっさと仕事を切り上げて充実したアフターファイブを過ごしたい
でしたよね。
あなたの解決したい問題は何ですか？
次回はまた
で Access VBA の新しい窓を開きましょう。
Microsoft Access 関連教材は次のリンクからどうぞ。
いつも記事を読んで頂きありがとうございます～
今日の調子はいかがですか？