本記事では 記事#26 でご紹介したバッチファイル
- %USERPROFILE%Toolsbat作成_東京都コロナウイルス_発症者推移グラフ.bat
から呼び出され VBA で SQL を操作して11個のテーブルを一気に作成する方法を中心に
以下の 東京都コロナウイルス 世代別 入院者数推移 グラフ
を描画するための元データ
- %USERPROFILE%\Tools_Data_Temp\xlsx\T3200_日付_年代別入院者数.xlsx
を作成するツール
- %USERPROFILE%\Tools\accdb\作成_T3200_日付_年代別入院者数_xlsx.accdb
の詳細についてご紹介します。
グラフ描画そのものは先ほどご紹介したバッチファイルの中で
この描画用の元データをグラフ描画用テンプレートに
転記することで行っています｡
このバッチファイルに興味のある方は 記事#26 も確認してくださいね｡
グラフ描画に向けた考え方
まずこのグラフ描画用のデータの入った Excel ブックを見ておきましょう。
Excelをお使いの方ならこの Excel ブックから
先ほどの折れ線グラフをどう描画するかは
ご存じですね～
そしてこちらがこの Excel ブックの元になっている
- T3200_日付_年代別入院者数 テーブル
です｡
この テーブルや､Excel ブック、グラフのデータ要素となるフィールド項目数は全部で12個ありますが、このグラフのデータ領域のフィールド項目の並び方が 記事#34 で参照した Excel ブックに似ていることに気づかれた方も多いのではないでしょうか｡
それもそのはずです｡
なぜならグラフ描画のためのフィールド項目数が3個から12個に増えていますが、テーブルやグラフの横軸となる 日付 の作り方やフィールド項目の 結合方法が 記事#34 で紹介した方法と全く同じだからです｡
また出来上がったテーブルの Excel ブックへのエクスポートのやり方も対象テーブル名が違うだけで全く同じです。
グラフのデータ区分
ところでこのグラフ､データ区分を元に何本のグラフを描くかは東京都がコロナ発症状況を年代別にサンプリングする際に、どう年代を区分するかによって決まっており、 記事#34 のように男女のどちらかというような一般的な区分ではありません。
そこでこのツールの中ではこの区分を明確にするため T0000_東京都コロナ発症状況_マスタ テーブル の 患者_年代 をグループ化し
- T3020_年代 テーブル
のレコード値を区分の識別子としています。
具体的にはこの T3020_年代 テーブルの 患者_年代 フィールドのレコード値のグループ化の結果として 年代区分を以下の
- 100歳以上
- 10歳未満
- 10代
- 20代
- 30代
- 40代
- 50代
- 60代
- 70代
- 80代
- 90代
11種類としています。
この T3020_年代 テーブルをよく見ると先頭に “-” というレコードがあります。おそらくこれは入院記録を取る際に年代が不明、あるいは未記入だったと思われますが､いずれにしてもこれはグラフ作成の対象から外した方が良さそうですのでここでは除外します。
それではこの連載の中で度々ご紹介している以下の T0000_東京都コロナ発症状況_マスタ テーブル からどうやってこのグラフ描画用の Excel ブックを作るかについて考えてみましょう。
この T0000_東京都コロナ発症状況_マスタ テーブル に含まれるデータは 記事#29 内のクエリ適用により既に 最新日付を含んだ400日分のデータとなっています。
テーブル内のレコードの特徴
さてグラフ描画用のデータを作成しようという観点で T0000_東京都コロナ発症状況_マスタ テーブル を眺めると以下の特徴が見てとれるのではないでしょうか。
- これらは特定日付に特定年代の人が入院したことを表すレコードである
- 特定日付に入院した特定年代の合計人数はあくまでその時々の状況により決まる
- 従ってこれらのレコードに含まれる日付は必ずしも連続とは限らない
グラフ用データをどう作るか
これらの特徴を踏まえ、このツールでは T0000_東京都コロナ発症状況_マスタ テーブルから11本のグラフを描画用データを作成する方法を次のような4つのブロックに分け作成しています。
|
|
|
|
この1番目、3番目､4番目のブロックについては既に別の記事で同様のことを行っていますので後ほど簡単にその方法をご紹介します。
そしてこの記事では2番目のブロックを暫定的に 特定年代毎入院者数テーブル作成処理 と呼び、これをどう処理するのかを中心にご紹介します。
特定年代毎入院者数テーブル作成処理
入力テーブル
以下は 特定年代毎入院者数テーブル作成処理 の入力として共通的に使用される T3010_日付_年代_入院者数 テーブルです。
参照テーブル
続いて以下がこの 特定年代毎入院者数テーブル作成処理 の中で参照される T3020_年代 テーブルです｡
このテーブルは患者の年代を特定するキーワードとして利用されます｡
出力テーブル
最後に以下が 特定年代毎入院者数テーブル作成処理 の出力として作成される 11個のテーブルです｡
- T3100_100歳以上_入院者数
- T3100_10歳未満_入院者数
- T3100_10代_入院者数
- T3100_20代_入院者数
- T3100_30代_入院者数
- T3100_40代_入院者数
- T3100_50代_入院者数
- T3100_60代_入院者数
- T3100_70代_入院者数
- T3100_80代_入院者数
- T3100_90代_入院者数
これらすべてのテーブルは全て 日付、入院者数 という共通のフィールドを持っていますが､その抽出条件､テーブル名はそれぞれ異なります｡
特定年代毎入院者数テーブル作成処理
ひな型クエリの作成
さてこの 特定年代毎入院者数テーブル作成処理 の中では以下の11個のテーブルを作成しないといけない訳ですが
- 100歳以上 の日付毎の入院者数は 特定日付 の 特定_年代 フィールド のレコード値が “100歳以上” のものを選択して作成
- 10歳未満 の日付毎の入院者数は 特定日付 の 特定_年代 フィールド のレコード値が “10歳未満” のものを選択して作成
- 10代 の日付毎の入院者数は 特定日付 の 特定_年代 フィールド のレコード値が “10代” のものを選択して作成
- 20代 の日付毎の入院者数は 特定日付 の 特定_年代 フィールド のレコード値が “20代” のものを選択して作成
- 30代 の日付毎の入院者数は 特定日付 の 特定_年代 フィールド のレコード値が “30代” のものを選択して作成
- 40代 の日付毎の入院者数は 特定日付 の 特定_年代 フィールド のレコード値が “40代” のものを選択して作成
- 50代 の日付毎の入院者数は 特定日付 の 特定_年代 フィールド のレコード値が “50代” のものを選択して作成
- 60代 の日付毎の入院者数は 特定日付 の 特定_年代 フィールド のレコード値が “60代” のものを選択して作成
- 70代 の日付毎の入院者数は 特定日付 の 特定_年代 フィールド のレコード値が “70代” のものを選択して作成
- 80代 の日付毎の入院者数は 特定日付 の 特定_年代 フィールド のレコード値が “80代” のものを選択して作成
- 90代 の日付毎の入院者数は 特定日付 の 特定_年代 フィールド のレコード値が “90代” のものを選択して作成
話を分かり易くするために、まずこのうちの1つ､例えば “10代” を選んでひな形クエリを作成してみましょう。
つまり T3010_日付_年代_入院者数.患者_年代 = “10代“ のとき T3010_日付_年代_入院者数 テーブル中の 日付 と 入院者数 フィールドを選択し、これを T3110_10代_入院者数 テーブルを作成するクエリをデザインビューで作成してみます｡
ひな形クエリの SQL 確認
続いてこのひな形クエリのデザインビューのタブ部分を
マウスで右クリック > SQL ビュー(Q) を選択
してこのクエリの SQL を確認すると次のようになっています｡
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3110_10代_入院者数
FROM T3010_日付_年代_入院者数
WHERE (((T3010_日付_年代_入院者数.患者_年代)="10代"));
ひな形クエリの SQL 理解
さてこのひな形クエリの SQL はどういったことを表しているのでしょうか。
この SQL の意味がわかれば一気に世界が広がりそうですね。
ということで、ここでこのひな形クエリの中で利用されている SQL (SELECT コマンド) の構成要素を抜粋してみました。
この SELECT コマンド は､1つまたは複数のテーブルからフィールドを選択するSQLコマンドで
続く INTO 句、FROM 句、WHERE 句 等と連携して指定フィールドを選択して表示したり、(INTO句がある場合)テーブルを作成したりします。
| 構文要素 | 意味 |
|---|---|
| SELECT コマンド | 続く 列名1,列名2,…で選択したいフィールドを指定します。 |
| INTO 句 | 選択したフィールドを元に指定されたテーブル名でテーブルを作ります。 |
| FROM 句 | フィールド取得元のテーブル名を指定します。 |
| WHERE 句 | テーブルから特定条件のレコードを抽出する際の条件式を指定します。 |
またSQLの中では列名は一般的に
- テーブル名.フィールド名
と記載されます｡
つまり先ほどの SQL
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3110_10代_入院者数
FROM T3010_日付_年代_入院者数
WHERE (((T3010_日付_年代_入院者数.患者_年代)="10代"));
は
- T3010_日付_年代_入院者数 テーブルを参照して
- T3010_日付_年代_入院者数.患者_年代 の値が “10代” に等しいとき
- T3010_日付_年代_入院者数.日付 と T3010_日付_年代_入院者数.入院者数 を出力フィールドとして
- T3110_10代_入院者数 テーブルを作成する
というクエリであることが確認できた訳です。
これらの SELECT コマンドの意味が理解できたことで VBA により 以下の T3020_年代 テーブル
から 患者_年代 のレコード値を順に読み出し
先ほどの SQL の 10代 の部分を入れ替えながらこれを実行すれば異なった11個のテーブルを作成するという方針を定めることができた訳です。
そして実際に以下の VB関数でそれを実現しています。
VBA 関数 作成_T31xx_年代別_入院者数
以下にこの目的のために作成した VBA 関数 作成_T31xx_年代別_入院者数 を紹介します。
この関数は後に紹介する
- M3200_作成_T3200_日付_年代別入院者数 マクロ
から呼び出され、引数はなく戻り値は常に C_SUCCESS(=0) です。
Function 作成_T31xx_年代別_入院者数() As Integer
Const C_SUCCESS As Integer = 0
Const C_FAILURE As Integer = 1
Const C_SQL_前 As String = "SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3100_"
Const C_SQL_中 As String = "_入院者数 FROM T3010_日付_年代_入院者数 WHERE (((T3010_日付_年代_入院者数.患者_年代)="""
Const C_SQL_後 As String = """));"
Dim referenceTableName As New ADODB.recordSet
referenceTableName.Open "T3020_年代", CurrentProject.Connection
DoCmd.SetWarnings WarningsOn:=False
Dim fld_患者_年代 As String
Dim sql_コマンド As String
Do Until referenceTableName.EOF
fld_患者_年代 = referenceTableName![患者_年代]
If fld_患者_年代 <> "-" Then
sql_コマンド = C_SQL_前 & fld_患者_年代 & C_SQL_中 & fld_患者_年代 & C_SQL_後
Debug.Print sql_コマンド
DoCmd.RunSQL sql_コマンド
End If
referenceTableName.MoveNext
Loop
referenceTableName.Close
Set referenceTableName = Nothing
作成_T31xx_年代別_入院者数 = C_SUCCESS
End Function
VBA 関数の説明
それでは以下にこの VBA コードの内容について簡単に説明しましょう。
| 行番号 | 説明 |
|---|---|
| 1 | 作成_T31xx_年代別_入院者数 をパラメータなしの整数型の関数として定義しています。 |
| 3-8 |
プログラム中で使用する定数を定義します。 |
| 10-11 | referenceTableName を新しい ADO オブジェクトとして定義し､現在実行中の Access 内の “T3020_年代” テーブルを参照テーブルとしてオープンします。 |
| 13 | DoCmd.SetWarnings コマンドでクエリ実行中の警告の発生を抑制します |
| 15-16 | 文字列変数 fld_患者_年代 、 sql_コマンド を定義します。 |
| 18-30 | 参照テーブル referenceTableName 内のレコードがなくなるまで1個づつ呼び出し以下の処理を繰り返します。 ・referenceTableName![患者_年代] からフィールド値を読みだし fld_患者_年代 にセットする ・fld_患者_年代 が “-” でないときのみ以下を実行 ・C_SQL_前、fld_患者_年代、C_SQL_中、fld_患者_年代、C_SQL_後 を文字結合し、sql_コマンド に設定 ・sql_コマンド を デバッグ用の確認としてイミディエイト画面に出力 ・DoCmd.RunSQL コマンドで sql_コマンド を実行 ・referenceTableName の次のレコードに移動 |
| 32-33 | 参照テーブル referenceTableName のオブジェクトをクローズ、開放します。 |
| 35 | この 関数 作成_T31xx_年代別_入院者数 の戻り値に C_SUCCESS(=0)を設定します。 |
| 37 | この 関数定義を終了します。 |
この VBA プログラムの中の
10-11 行でのテーブルのオープンの方法
18,28-30行でのテーブル内レコードの順次読みだしの方法
20行のテーブル内フィールドの参照方法: referenceTableName![患者_年代] は referenceTableName つまりここでは 現在実行中の Access 内の “T3020_年代” テーブル の 患者_年代 フィールドから読みだしたレコード値
は Access VBA で指定テーブルから指定フィールドのレコード値を読みだす際のお約束的な書き方なのでぜひ覚えておきましょう。
VBA 関数が生成する SQL
ちなみに 24行めの Debug.Print 文によってイミディエイト画面に sql_コマンド に次のような文字列が出力されます｡
この VBA 関数が期待どうりに sql_コマンド を生成していることが確認できますね｡
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3100_100歳以上_入院者数 FROM T3010_日付_年代_入院者数 WHERE (((T3010_日付_年代_入院者数.患者_年代)="100歳以上"));
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3100_10歳未満_入院者数 FROM T3010_日付_年代_入院者数 WHERE (((T3010_日付_年代_入院者数.患者_年代)="10歳未満"));
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3100_10代_入院者数 FROM T3010_日付_年代_入院者数 WHERE (((T3010_日付_年代_入院者数.患者_年代)="10代"));
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3100_20代_入院者数 FROM T3010_日付_年代_入院者数 WHERE (((T3010_日付_年代_入院者数.患者_年代)="20代"));
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3100_30代_入院者数 FROM T3010_日付_年代_入院者数 WHERE (((T3010_日付_年代_入院者数.患者_年代)="30代"));
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3100_40代_入院者数 FROM T3010_日付_年代_入院者数 WHERE (((T3010_日付_年代_入院者数.患者_年代)="40代"));
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3100_50代_入院者数 FROM T3010_日付_年代_入院者数 WHERE (((T3010_日付_年代_入院者数.患者_年代)="50代"));
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3100_60代_入院者数 FROM T3010_日付_年代_入院者数 WHERE (((T3010_日付_年代_入院者数.患者_年代)="60代"));
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3100_70代_入院者数 FROM T3010_日付_年代_入院者数 WHERE (((T3010_日付_年代_入院者数.患者_年代)="70代"));
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3100_80代_入院者数 FROM T3010_日付_年代_入院者数 WHERE (((T3010_日付_年代_入院者数.患者_年代)="80代"));
SELECT T3010_日付_年代_入院者数.日付, T3010_日付_年代_入院者数.入院者数 INTO T3100_90代_入院者数 FROM T3010_日付_年代_入院者数 WHERE (((T3010_日付_年代_入院者数.患者_年代)="90代"));
ツール内テーブルの概要
これ以降はツール内で入出力テーブルとして参照されているテーブルの概要を紹介しますので興味のある方は以降の記事もご覧ください｡
T3000_日付
まず以下は Q3000_作成_T3000_日付 クエリによって作成される T3000_日付 テーブルです。
このテーブルは
- 日付
フィールドが含まれており、このフィールドがグラフの横軸、および他のテーブルと連結する際のキーワードになります。
T3010_日付_年代_入院者数
次に Q3010_作成_T3010_日付_年代入院者数 クエリによって作成される T3010_日付_年代_入院者数 テーブルです。このテーブルは
- 日付
- 患者_年代
- 入院者数
フィールドから構成されており、特定年代毎入院者数テーブル作成 の入力として特定日付の全ての年代の入院者数を含むテーブルです。
T3020_年代
次に Q3020_作成_T3020_年代 クエリによって作成される T3020_年代 テーブルです。このテーブルは
- 患者_年代
フィールドの中に全ての年代を識別するキーワードを含みます。
T3100_xxxx_入院者数
次に
この記事のメインテーマである (VBA 関数) 作成_T31xx_年代別_入院者数 によって作成される T3100_xxxx_入院者数 テーブルです。
正確には以下の11個のテーブル
- T3100_100歳以上_入院者数
- T3100_10歳未満_入院者数
- T3100_10代_入院者数
- T3100_20代_入院者数
- T3100_30代_入院者数
- T3100_40代_入院者数
- T3100_50代_入院者数
- T3100_60代_入院者数
- T3100_70代_入院者数
- T3100_80代_入院者数
- T3100_90代_入院者数
ですが､これらは テーブル名にある年代を表す識別子が違うだけで、同じテーブル構造を持っており
- 日付
- 入院者数
フィールドから構成されています。
T3200_日付_年代別入院者数
最後に Q3200_作成_T3200_日付_年代別入院者数 という結合クエリが出力する T3200_日付_年代別入院者数 テーブルを紹介します。
このテーブルは 日付 をキーにした結合クエリの実行結果として
- 日付
- 10歳未満_入院数合計
- 10代_入院数合計
- 20代_入院数合計
- 30代_入院数合計
- 40代_入院数合計
- 50代_入院数合計
- 60代_入院数合計
- 70代_入院数合計
- 80代_入院数合計
- 90代_入院数合計
- 100歳以上_入院数合計
という12個のフィールドから構成されています。
ツール内マクロの概要
これ以降はツール内で定義されているマクロの概要を紹介しますので興味のある方は以降の記事もご覧ください｡
autoexec
autoexec はこのツール
- %USERPROFILE%\Tool\saccdb\作成_T3200_日付_年代別入院者数_xlsx.accdb
の実行全体を制御するマクロです｡
このツール起動されると autoexec 内に記述されたマクロ
- M0000_Call_LinkExternalDbTable
- M3200_作成_T3200_日付_年代別入院者数
- M3210_Call_ExportTableToBook
を順に自動実行し､最後にこのAccessを終了します｡
M0000_Call_LinkExternalDbTable
M0000_Call_LinkExternalDbTable は
autoexec マクロから起動されるマクロで
このマクロ内でしらかば堂のライブラリ関数 LinkExternalDbTable を使用して
- %USERPROFILE%Toolsaccdb作成_T0000東京都コロナ発症状況マスタ.accdb
内の
- T0000東京都コロナ発症状況_マスタ テーブル
に対するリンク情報を設定します｡
ライブラリ関数 LinkExternalDbTable の詳細について興味のある方は以下の記事も参照ください。
M3200_作成_T3200_日付_年代別入院者数
M3200_作成_T3200_日付_年代別入院者数 は
autoexec マクロから起動され、このツールの中心をなすマクロです。
以下のように
- Q3000_作成_T3000_日付
- Q3010_作成_T3010_日付_年代_入院者数
- (VBA 関数) 作成_T31xx_年代別_入院者数
- Q3200_作成_T3200_日付_年代別入院者数
を順番に実行します。
M3210_Call_ExportTableToBook
M3210_Call_ExportTableToBook は autoexec マクロから起動され
M3200_作成_T3200_日付_年代別入院者数
が作成した
- T3200_日付_年代別入院者数 テーブル
を
- %USERPROFILE%\Tools_Data_Temp\xlsx\T3200_日付_年代別入院者数.xlsx
ヘエクスポートします。
ライブラリ関数 ExportTableToBook の詳細について興味のある方は以下の記事も参照ください。
ツール内クエリの概要
これ以降はツール内で定義されているクエリの概要を紹介しますので興味のある方は以降の記事もご覧ください｡
Q3000_作成_T3000_日付
Q3000_作成_T3000_日付 は M3200_作成_T3200_日付_年代別入院者数 から起動されるクエリで
T0000_東京都コロナ発症状況_マスタ テーブル内に含まれる 公表_年月日 フィールドをグループ化し、グラフの横軸となる T3000_日付 テーブルを作成します。
このレコードをグループ化するクエリの作成方法については必要に応じて 記事#21 もご覧ください。
Q3010_作成_T3010_日付_年代入院者数
Q3010_作成_T3010_日付_年代_入院者数 は M3200_作成_T3200_日付_年代別入院者数 から起動されるクエリで
T0000_東京都コロナ発症状況_マスタ テーブル内に含まれる 公表_年月日 フィールド、 患者_年代 フィールドをグループ化し、 入院者フラグ フィールドのレコード値を合計して T3010_日付_年代_入院者数 テーブルを作成します。
このレコードをグループ化するクエリの作成方法については必要に応じて 記事#21 もご覧ください。
Q3020_作成_T3020_年代
Q3020_作成_T3020_年代 は M3200_作成_T3200_日付_年代別入院者数 から起動されるクエリで
T0000_東京都コロナ発症状況_マスタ テーブル内に含まれる 患者_年代 フィールドをグループ化して T3020_年代 テーブルを作成します。
このレコードをグループ化するクエリの作成方法については必要に応じて 記事#21 もご覧ください。
Q3200_作成_T3200_日付_年代別入院者数
Q3200_作成_T3200_日付_年代別入院者数 は M3200_作成_T3200_日付_年代別入院者数 から起動されるクエリで
T3000_日付 テーブルの 日付 フィールドと
- (VBA 関数) 作成_T31xx_年代別_入院者数
が作成した以下の11個のテーブル
- T3100_100歳以上_入院者数
- T3100_10歳未満_入院者数
- T3100_10代_入院者数
- T3100_20代_入院者数
- T3100_30代_入院者数
- T3100_40代_入院者数
- T3100_50代_入院者数
- T3100_60代_入院者数
- T3100_70代_入院者数
- T3100_80代_入院者数
- T3100_90代_入院者数
の全てを そのテーブル内の 日付 フィールドで結合し、日付 とそのそれぞれの 入院者数 を識別子付で出力し T3200_日付_年代別入院者数 テーブルを作成します。
この複数テーブルの結合クエリの作成方法については必要に応じて 記事#34 もご覧ください。
まとめ
今回は VBA で SQL を操作して多数のテーブルを一気に作成する方法を以下のポイントを中心に紹介しました。
- グラフ用データ作成に向けての考え方
- データ区分の抽出方法
- ひな型クエリを作成とSQLの 確認
- SQL(SELECT文) の簡単な説明
- SQLを生成するVBA コードの紹介
さてこの記事全体の目標は
- 今の状況から抜け出してもっと前向きな仕事に取り組みたい
- さっさと仕事を切り上げて充実したアフターファイブを過ごしたい
でしたよね。
あなたの解決したい問題は何ですか？
次回はまた Access VBA の新しい窓を開きましょう。
Microsoft Access 関連教材は次のリンクからどうぞ。
いつも記事を読んで頂きありがとうございます～
今日の調子はいかがですか？