【SQL学習入門】5回目:Accessテーブルの構成を編集できるデータ定義クエリ

Accessで使えるSQL文についての解説シリーズの5回目です。
前回の記事はこちら。
≫ 【SQL学習入門】4回目:Accessクエリの数を減らすSQL文の書き方

「SQL学習入門」というシリーズで、AccessクエリのSQLビューを使いこなす方法について紹介しています。

前回まで解説した「SQL文を使ってデータの参照したり編集する方法」を活用すれば、ほとんどのデータを操作できます。
しかし、テーブルを作ったり、データ型を変えたりなど、構成を変えることができれば、更に細かいデータの加工を行えます。

本記事では、データ定義クエリを実行させた時の動きと、どのような場面で使えるのかを紹介します。

ある程度Access SQLに使い慣れている方は、是非実務に活用してみてください。
一方、SQL文を学びたての方は、「そんな使い方もあるのか」と頭の片隅に入れていただければ十分です。
きっと後で役に立ちます。

今回の内容を学習するにあたり、サンプルデータベースを是非ご活用ください。
≫購買管理DB_データ定義クエリ

データ定義クエリとは

サンプルデータベースを開くと、デザインビューに似たようなアイコンのクエリがありますが、これが「データ定義クエリ」です。

データ定義クエリは、選択クエリなど他のクエリとは全く違う処理を行います。
データ定義クエリを使うと下のような処理を行えます。

• テーブルを作る
• フィールドを名前やデータ型を変更
• テーブルのフィールドを追加する
• テーブルを削除する
• インデックスを変更する

データ定義クエリは、アイコンをクリックするだけでクエリの処理を実行することができます。
どちらかというと、更新クエリなどの「アクションクエリ」と近いかもしれませんね。

デザインビューを開くと、SQLビューで内容を確認することができます。

SQLビューを開いたまま、リボンの「実行」メニューで処理を行うこともできます。

警告が表示されますが、そのまま「はい」をクリックすれば、処理が進行します。

データクエリのSQL文は、VBAでもそのまま実行できますので、通常はオブジェクトブラウザに保存させません。
モジュールにコーディングして残すか、コードマスタとして専用のテーブルに保存させておくとよいでしょう。

データ定義クエリの大まかな使い方を知って頂いたところで、詳しい動作について解説していきます。
ただし、リストの最後に記載している「インデックスを変更する」のみは、あまり活用する場面がありませんので今回は割愛しました。

CREATE TABLE文でテーブルを作る

CREATE TABLE文を使うことで新しいテーブルを作ることができます。
試しに「T001小分類マスタ」と同じ構成のテーブルをデータ定義クエリで作ってみます。
下の文はサンプルデータベースの「01_テーブル作成クエリ」のSQL文です。

CREATE TABLE T00小分類マスタ_COPY (小分類ID COUNTER PRIMARY KEY, 小分類名 VARCHAR(50),中分類ID LONG);

クエリを実行すると、新しいテーブルが追加されました。

Accessには「テーブル作成クエリ」というものがありますが、「SELECT ～ INTO」といったSQL文が使われています。
といった感じでCREATE文を使わなくても新しいクエリを使うことができますが、両方使いこなせると場面によって器用に使い分けることができます。

ALTER TABLE文を使ってテーブル構成を編集する

ALTER TABLE文を使うと、下のような処理を行うことができます。

• フィールドを新しく追加する
• データ型を変更する
• フィールドを削除する

残念ながら、AccessのALTER TABLE文ではフィールド名を変更することができないようです。

ADD COLUMNでフィールドを追加する

T00小分類マスタ_COPYテーブルに「備考」を追加してみましょう。
サンプルデータベースの「02_フィールド追加」のSQL文は下のとおりです。

ALTER TABLE T00小分類マスタ_COPY ADD COLUMN 備考 TEXT(25);

クエリを実行すると、備考フィールドが無事に追加されました。

ALTER COLUMN でデータ型を変更する

備考フィールドの文字列数を25から50に変更してみます。
サンプルデータベースの 「03_データ型変更」 のSQL文は下のとおりです。

ALTER TABLE T00小分類マスタ_COPY ALTER COLUMN 備考 TEXT(50);

テーブルデザインビューを確認すると、備考の文字数が25から50になっていることが確認できました。

DROP COLUMNでフィールドを削除する

備考フィールドを削除してみましょう。
サンプルデータベースの 「04_フィールド削除」 のSQL文は下のとおりです。

ALTER TABLE T00小分類マスタ_COPY DROP COLUMN 備考;

クエリを実行すると、備考フィールドが削除されているのが確認できました。

DROP TABLE文で不要なテーブルを削除する

DROP TABLEを使ってT00小分類マスタ_COPYを削除してみます。
サンプルデータベースの 「05_テーブル削除」 のSQL文は下のとおりです。

DROP TABLE T00小分類マスタ_COPY;

クエリを実行すると、テーブルが削除されているのが確認できました。

データ定義クエリはどんな場面で使えるのか

これまでの解説で、データ定義クエリでテーブルの構成を編集できるということについてはある程度理解していただけたかと思います。

では、実務ではどのような時にデータ定義クエリを使うのが有効なのでしょうか？
一番有効なのは、テーブルのデータを基にして複雑な計算を行いたい時です。

• 週や月単位で何らかの集計を行いたい
• 原価計算で商品別に経費を配賦したい
• 実績を元にして今後の予想を立てたい

UPDATE文やINSERT文などのアクションクエリを使って一発で答えを導き出せればデータ定義クエリは必要ないのですが、いくつかのテーブルを経由しないと答えにたどり着けない場合は、データ定義クエリがとても有効です。
集計している時だけ仮のテーブルを作り、不要になったら削除できるので、データベースの容量も圧迫しません。

オブジェクトブラウザに仮の中間テーブルがたくさん表示されていたらとても見づらいですよね？
そういった意味からも、データ定義クエリを上手に使えることかなり便利です。

他にも、AccessのデータをSQL SERVERなどのデータベースに反映させたい時、データ型が合っていないとエラーで止まります。
ALTER COLUMNでデータ型を合わせておけば、スムーズに処理を実行できます。

まとめ

今回は、Accessで使えるデータ定義クエリを紹介しました。
一般的なSQLだけでは実行できない集計については、データ定義クエリを織り交ぜることで解決できることが多いです。
システムに溜まっているデータを有効活用したい方については、データ定義クエリを使った集計を検討してみてはいかがでしょうか？
きっと問題解決の何かのヒントになるでしょう。

次回は、DISTINCTなどのような「マイナーな使い方だけど結構便利に使える構文」について解説します。
≫ 【SQL学習入門】6回目:あまり知られていないけど便利なSQL構文(Access)