本記事では 他の Access ファイルで手間をかけて作ったクエリ操作 をインポートし再利用する方法や関連するSQL構文についてご紹介します。
目次
他の Access ファイルのクエリ操作 をインポートしよう
Access でツールを作っていて他の Access ファイルで作ったクエリ操作等を再利用したいときがありますよね。
Access にはこのようなことができるように、他の Access ファイルので作ったテーブル、クエリ、フォームといったオブジェクトをインポートする機能が用意されています。
具体的には Access のリボンメニューから
新しいデータソース ＞ データベースから(D) > Access
を順に選択し
開いた 外部データの取り込み Access データベース 画面でインポートしたいクエリ等のオブジェクトの入っている Access ファイルの フォルダ を選択し
さらにその対象 Access ファイルを選択し 開く(O) ボタンを押すと
以下の 外部データの取り込み Access データベース 画面になるので、ここで OK ボタンを押してその Access ファイルを開くと
以下の オブジェクトのインポート 画面が出てくるので、インポートしたいオブジェクト
- テーブル
- クエリ
- フォーム
- レポート
- マクロ
- モジュール
の タブ を選択し（例えば クエリ をインポートしたいときは クエリ タブを選択し）その下のリストからインポートしたいオブジェクト（この場合はクエリ）の名前を１つずつ選択し OK ボタンをクリックして確定後 OK ボタンをクリックするといった手順になります。
また複数のオブジェクトをインポートしたいときはこの操作を繰り返します。
本記事では以下の画像に現れている
- Q1010_作成T1010最終日付
- Q1020_作成_T1020_日付_退院者フラグ_入院者フラグ
という２つのクエリをインポートしたので指定クエリを変えこの手順を２回実施しています。
16_01.accdb に対するリファクタリングの内容
それではこの 他の Access ツールからクエリ操作をインポートし再利用する やり方がわかったところで 記事#25
の中で紹介した Access ツール 16_01.accdb の15番目のリファクタリング項目
| 項番 | 解決したい課題 | リファクタリングの方針 |
|---|---|---|
| 15 |
日付(400日分)、日付毎の入院者数抽出機能の移動 | 16_01.accdb の T0000_東京都コロナ発症状況_マスタ テーブル に日付(400日分)、日付毎の入院者数抽出機能を移動します。 |
の内容をもう少し具体的に紹介しましょう。
このリファクタリングの方針をもう少し具体的に紹介すると、ここでやりたいのは以下の
16_01.accdb 改め 作成T0000東京都コロナ発症状況_マスタ.accdb
の中の T0000_東京都コロナ発症状況_マスタ テーブルに
- 入院者フラグ フィールドを追加すること
- 格納されるレコードが参照元の T0000_東京都コロナ発症状況_マスタ テーブルに含まれる最新日付(つまりこのテーブルに含まれる最も新しいサンプル日付)と比較して400日以前の 公表_年月日 をもつレコードを含まないようにすること
です。
これらはどのように実現しているのでしょうか？
T0000_東京都コロナ発症状況_マスタ へフィールド追加する
まず T0000_東京都コロナ発症状況_マスタ への 入院者フラグ フィールドの追加については、以下のように 入院者フラグ フィールドに値 1 を代入して追加するだけなので比較的簡単です。
T0000_東京都コロナ発症状況_マスタ から最新日付の400日前以前のレコードを削除する
続いて格納されるレコードが参照元の T0000_東京都コロナ発症状況_マスタ テーブルに含まれる最新日付(つまりこのテーブルに含まれる最も新しいサンプル日付)と比較して400日以前の 公表_年月日 をもつレコードを含まないようにすることについてはどうでしょう｡
この部分についてはさらに次のようにクエリを分解する必要があります｡つまり
- T0000_東京都コロナ発症状況_マスタ テーブルに含まれるレコード内で 公表_年月日 の最大値を求める
- T0000_東京都コロナ発症状況_マスタ に含まれるレコードのうち 公表_年月日 のフィールド値が 公表_年月日 の最大値と比較して400日以前の 公表_年月日 をもつレコードにマーキングをする(フィールド値をNullにする)
- T0000_東京都コロナ発症状況_マスタ に含まれるレコードのうち､ 公表_年月日 のフィールド値がマーキングをされている(フィールド値をNullの)レコードを削除する
という複数のクエリ操作でこれを行います。
この 1. に対するクエリ操作は
の中で紹介したように Access ツール 18_02.accdb 内の
- Q1010_作成T1010最終日付
が、これを行っていますので、今回はこのクエリをそのままインポートして再利用します。
また 2. のクエリ操作は同様に
の中で Access ツール 18_02.accdb 内の
- Q1020_作成_T1020_日付_退院者フラグ_入院者フラグ
が式ビルダを使って閾値に対する次の条件式を作っていますので
<=DateAdd(“d”,-400,[T1010_最終日付]![公表_年月日の最大])
これもこのクエリを 18_02.accdb からインポートしたものを再利用し、少しだけ手をいれます。
最後に 3. のクエリ操作は直前のマーキングの結果を元に今回新しく作成します。
ねえねえしらかば堂さ～ん
ちょっと聞いていいですかぁ～♪
最終日の400日前以前のデータを削除するのに
2. のクエリでマーキングして
3. のクエリでマーキングされてるものを削除
してるみたいなんですけど
何でいっぺんに削除しないんですかぁ？
超めんどうなんですけどぉ～
うっ！ 鋭い！
本当は自分もそうしたかったんですけどね～
でも実際にやってみるとわかるんですが
2. の操作を[更新]から[削除]に変更してレコードを削除しようとすると
Access に以下のようなエラーが出て怒られてしまうんですよ～
後でさらっと紹介しますが
この一発削除クエリをうまく動かすためには
Access で利用されている SQL の文法やサブクエリという
少し複雑な技を駆使する必要があるんです。
なのでここでは簡単のために分けて実装しました～
リファクタリングされたクエリ
それではリファクタリングされたクエリが実際どのようなものになるのか順にご紹介しましょう｡
Q0000_作成_T0000_東京都コロナ発症状況_マスタ
まず以下がもともとこの
16_01.accdb 改め 作成_T0000_東京都コロナ発症状況_マスタ.accdb
の中にあった
Q0000_作成_T0000_東京都コロナ発症状況_マスタ
クエリです。
このクエリへのリファクタリングとしては赤い枠で囲んだ部分で 固定値 1 をもつ 入院者フラグ フィールドを追加しています｡
Q0000_作成_T0000_東京都コロナ発症状況_マスタ クエリ
Q1010_作成_T1010_最終日付 クエリ
次に Q1010_作成_T1010_最終日付 クエリです。
このクエリは 18_02.accdb 内にあった Q1010_作成_T1010_最終日付 を作業中の 16_01.accdb 改め 作成_T0000_東京都コロナ発症状況_マスタ.accdb にそのままインポートしたもので、このクエリにより後に削除処理を行うためのしきい値 T1010_最終日付 テーブルを作成しています｡
このインポート操作によりこの 作成T0000東京都コロナ発症状況_マスタ.accdb に T0000_東京都コロナ発症状況_マスタ 内に含まれるレコード内の日付の最大値から T1010_最終日付 というテーブルを作成する機能を簡単に移植できました。
Q1010_作成_T1010最終日付 クエリ
Q1020_更新_T0000_東京都コロナ発症状況_マスタ_400日以前
次に Q1020_作成_T1020_日付退院者フラグ入院者フラグ クエリです。
このクエリは インポート元の 18_02.accdb 内の Q1020_作成_T1020_日付退院者フラグ入院者フラグ クエリ を流用して作ったもので
レコード内に含まれる最大(最新)日付より400日前の日付を抽出するための条件式
>DateAdd(“d”,-400,[T1010_最終日付]![公表_年月日の最大])
を利用して、これを更新クエリに変更し、更新値として Null を設定したものです。
Q1020_作成_T1020_日付退院者フラグ入院者フラグ クエリ から抽出条件を流用して作った
Q1020_更新_T0000_東京都コロナ発症状況_マスタ_400日以前 クエリ
先ほど SQL を紹介しましたので､このクエリのSQL構文を‘ご参考で紹介します。
UPDATE T0000_東京都コロナ発症状況_マスタ, T1010_最終日付 SET T0000_東京都コロナ発症状況_マスタ.公表_年月日 = Null
WHERE (((T0000_東京都コロナ発症状況_マスタ.公表_年月日)<=DateAdd(“d”,-400,[T1010_最終日付]![公表_年月日の最大])));
クエリの SQL構文 がみたいときは
クエリデザインビュー上部のフィールドリスト表示エリアの
何もないところで右ボタンクリックして現れる
以下のコンテキストメニューで SQLビュー(Q) を選択することで
このクエリのSQL構文を参考として見ることができます
SQLビュー(Q)
Q1030_削除_T0000_東京都コロナ発症状況_マスタ_400日以前
そしてこちらが Q1020_更新_T0000_東京都コロナ発症状況_マスタ_400日以前 クエリで
T0000_東京都コロナ発症状況_マスタ.公表_年月日 = Null
とマーキング(設定)されたレコードを全て削除する新設クエリです。
このクエリにより T0000_東京都コロナ発症状況_マスタ テーブルから 公表_年月日の最大 日から400日以前のレコードが全て削除される訳です。
Q00Q1030_削除T0000東京都コロナ発症状況_マスタ_400日以前 クエリ
ここでも先ほど同様、このクエリの SQL 構文を紹介しておきます。
DELETE T0000_東京都コロナ発症状況_マスタ.公表_年月日
FROM T0000_東京都コロナ発症状況_マスタ
WHERE (((T0000_東京都コロナ発症状況_マスタ.公表_年月日) Is Null));
サブクエリを使用した削除クエリ【参考情報】
このクエリはこの紹介しているツールの中では実際に使用していませんが上記の2つのクエリ
- Q1020_更新_T0000_東京都コロナ発症状況_マスタ_400日以前
- Q1030_削除_T0000_東京都コロナ発症状況_マスタ_400日以前
を先ほど触れた‘サブクエリ を使って１つにまとめたものです｡
DELETE T0000_東京都コロナ発症状況_マスタ.[公表_年月日], *
FROM T0000_東京都コロナ発症状況_マスタ
WHERE (((T0000_東京都コロナ発症状況_マスタ.[公表_年月日]) In (select T0000_東京都コロナ発症状況_マスタ.公表_年月日 AS 日付
FROM T0000_東京都コロナ発症状況_マスタ, T1010_最終日付
WHERE (((T0000_東京都コロナ発症状況_マスタ.公表_年月日)<=DateAdd(“d”,-400,[T1010_最終日付]![公表_年月日の最大]))))));
紹介するだけで頭痛くなってきました(笑)
この In (select … ) で囲まれている部分が サブクエリと呼ばれる技法を用いて記述された部分です。要はクエリの中に別のクエリを記述するんですね。
リファクタリングされたマクロ
次にリファクタリングしたマクロについても紹介しておきましょう。
M0000_作成_東京都コロナ発症状況_マスタ
M0000_作成_東京都コロナ発症状況_マスタ は 16_01.accdb にもともとあったマクロですがここまで紹介したクエリへの修正・追加を実際の動きに反映するために赤字で囲まれた以下のアクションを追加しています。
- クエリを開く Q1010_作成_T1010_最終日付
- クエリを開く Q1020_更新_T0000_東京都コロナ発症状況_マスタ_400日以前
- クエリを開く Q1030_削除_T0000_東京都コロナ発症状況_マスタ_400日以前
M0000_作成_東京都コロナ発症状況マスタ マクロ
まとめ
いかがでしたか？
本記事では 記事#25 でご紹介したリファクタリング方針に基づき 記事#28 に続き
- 他の Access ファイルで作成したクエリをインポートして再利用する方法
- 16_01.accdb に対する具体的なリファクタリングの内容
- リファクタリングされたクエリ
- リファクタリングされたマクロ
について紹介しました｡
また参考情報としてこれら クエリ の SQL構文 での表現方法､サブクエリ のさわりの部分を紹介しました｡
次の記事では 記事#25 でご紹介したリファクタリング方針に基づき次の 18_02.accdb に対する以下のリファクタリング項目の方法について具体的に紹介します。
| 項番 | 解決したい課題 | リファクタリングの方針 |
|---|---|---|
| 2 | デスクトップの整理とツールの名称変更 | デスクトップ上に配置されているツールを以下のフォルダに移動し名称変更します。 |
| 5 | 18_02.accdb ⇒ %USERPROFILE%\Tools\accdb\作成_T1040_日付_退院者数_入院者数_退院率_xlsx.accdb |
|
| 9 | 不要な警告ダイアログの抑制 | 18_02.accdb から T1040_日付_退院者数_入院者数_退院率.xlsx が作成された メッセージダイアログの出力を削除します。 |
| 12 | ツールからの絶対パス情報を相対パス情報に変更 | 18_02.accdb の 16_01.accdb 内 T0000_東京都コロナ発症状況_マスタ テーブルへのリンク処理を しらかば堂のユーザー定義関数(VBA) LinkExternalDbTable による相対パス指定に変更します。 |
| 13 | 18_02.accdb の T1040_日付_退院者数_入院者数_退院率 テーブルの T1040_日付_退院者数_入院者数_退院率.xlsx への エクスポート処理を しらかば堂のユーザー定義関数(VBA) ExportTableToBook による相対パス指定に変更します。 |
さてこの記事全体の目標は
- 今の状況から抜け出してもっと前向きな仕事に取り組みたい
- さっさと仕事を切り上げて充実したアフターファイブを過ごしたい
でしたよね。
あなたの解決したい問題は何ですか？
次回はまた
で Access VBA の新しい窓を開きましょう。
Microsoft Access 関連教材は次のリンクからどうぞ。
いつも記事を読んで頂きありがとうございます～
今日の調子はいかがですか？