【Access】複数フィールドを１つのフィールドにまとめるユニオンクエリ

前回書いた記事からAccessを使ったシステム構築を進めてきました。メインとして使っているシステムで不足した部分を補うシステムを作ることが目的です。

ある程度形になりましたので、ここいらでやったことを書き留めるターンに移ります。

この記事では複数フィールドに分かれている値を１つのフィールドにまとめるユニオンクエリについて取り上げます。

前提となるデータ
ユニオンクエリ
1. ユニオンクエリを加工するにはテーブルにする必要あり
2. 所属終了日を埋める処理
一連のクエリを実行するマクロ

前提となるデータ

「T_職員マスタ」というテーブルを用意しました。

各職員の所属部署と所属開始日と所属終了日が記録されています。所属1～3までのフィールドがあり、所属1から異動になると、所属2に次の異動先が入るといった形です。

フィールドを増やして記録していくのはデータベース的にあまりよくないと思うのですが、実際に僕が取り扱った案件ではメインシステムからこの形でデータがエクスポートされて、どうにかしてAccessで使えるようにしないといけませんでした。データの連携とは一筋縄でいかんものです。

処理日時点でどこに所属しているかを判定するように作るのですが、これだと判定するフィールドが所属開始日1～所属終了日3までの6フィールドを比較しないといけません。ダルいというか無理じゃね？

そういうわけで所属1～3を所属、所属開始日1～3を所属開始日、所属終了日1～3を所属終了日としてそれぞれ１つのフィールドにまとめてやるために、どうやらユニオンクエリが有効なようです。

ちなみに所属は数値が入っていますが、これらは別に「T_所属マスタ」を作ってリレーションを組むことにします。

ユニオンクエリ

ユニオンクエリを作るにはクエリデザイン等で新しいクエリを作成し、メニューでユニオンを選択します。

選択するとSQLビューに切り替わります。ユニオンクエリはSQLの知識がないとダメっぽいですね。今回のケースでは下記のようにSQLを記述します。

SELECT ID, 所属1 AS 所属, 所属開始日1 AS 所属開始日, 所属終了日1 AS 所属終了日 FROM T_職員マスタ WHERE [T_職員マスタ]![所属1] <> Null
UNION SELECT ID, 所属2 AS 所属, 所属開始日2 AS 所属開始日, 所属終了日2 AS 所属終了日 FROM T_職員マスタ WHERE [T_職員マスタ]![所属2] <> Null
UNION SELECT ID, 所属3 AS 所属, 所属開始日3 AS 所属開始日, 所属終了日3 AS 所属終了日 FROM T_職員マスタ WHERE [T_職員マスタ]![所属3] <> Null;

これらを分解するとこんな感じ。SQLはあまり知識がないので解釈が違ってたらすみません。

SELECT ID: T_所属マスタから「ID」を選択
所属1 AS 所属: 「所属1」を「所属」として取り扱う
所属開始日1 AS 所属開始日: 「所属開始日1」を「所属開始日」として取り扱う
所属終了日1 AS 所属終了日: 「所属終了日1」を「所属終了日」として取り扱う
FROM T_職員マスタ: T_職員マスタを参照
WHERE [T_職員マスタ]![所属1] <> Null: T_職員マスタの所属1がNullでないレコードに対して実行
UNION SELECT ID: 以下の選択についても上記の選択に結合する

SQL分の最後は「;」で終わります。これさえ守れば所属4、所属5と追加していくことも可能です。

実行結果がこちら。IDは所属1と所属2の両方に入力があるので２つレコードがありますね。これで結合ができました。

ユニオンクエリを加工するにはテーブルにする必要あり

所属終了日がNullだと処理日との比較ができなくなってしまいます。

所属終了日がNullの場合はかなり遠い未来の日付で埋めてやりたいところですが、ユニオンクエリ自体は加工することができます。

何らかの加工が必要な場合はテーブルにする必要があります。作成クエリを使います。

クエリデザインから作成し、メニューの「テーブルの作成」をクリックします。

テーブルの作成ウィンドウが表示されるので、作成する新しいテーブル名を「T_所属情報」にします。

その後のクエリデザインの画面で、テーブルの追加から先ほど作成したQ_所属情報_ユニオンを追加し、「*」をダブルクリックしてすべてのフィールドを移すように設定して実行します。

テーブルができました。

このクエリは「Q_所属情報_作成」という名前を付けて保存しておきます。

ちなみに既にT_所属情報がある状態でQ_所属情報_作成を実行すると、既にあるT_所属情報は削除されて新たにテーブルを作成するような流れになります。

所属終了日を埋める処理

あとは本来の目的である所属終了日を埋める処理を作ります。

クエリを作成したらメニューより更新を選び、先ほど作成したT_所属情報を追加、所属終了日を選択し、レコードの更新を「#2099/12/31#」、抽出条件を「Is Null」にします。

日付は迎えることのない日付であればいつでもOKですが、この条件だと2100年を迎えると正常に動かなくなるので注意です。それまでシステムを使ってるかどうか知りませんが。こうして2000年問題は起きるのですね。

できました。このクエリも「Q_所属終了日埋め_更新」と名前を付けて保存しておきます。

一連のクエリを実行するマクロ

職員マスタの所属情報をいじる度にこの一連のクエリを実行しないといけないわけですが、これもマクロでひとまとめにできます。

Private Sub 所属情報更新()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Q_所属情報_ユニオン"
    DoCmd.Close acQuery, "Q_所属情報_ユニオン", acSaveYes
    DoCmd.OpenQuery "Q_所属情報_作成"
    DoCmd.OpenQuery "Q_所属終了日埋め_更新"
    DoCmd.SetWarnings True
End Sub

テーブルやクエリを開いたり削除したりとメッセージが出ていちいち止まってしまうので、「DoCmd.SetWarnings False」でメッセージを非表示状態でクエリを順番に実行していきます。

ユニオンクエリは実行後クエリを開いてしまうので、実行後すぐに保存して閉じる動作があります。

こんな感じでユニオンクエリを使えるようになりました。よかったですね。