前回までの過去３記事は、SQL文の書き方の基礎である「SELECT」「INSERT」「UPDATE」「DELETE」の４つのSQL文を紹介してきました。
４回目にあたる今回は、Accessでデータベースを構築する時にほとんどの人がぶつかると思われる問題を解決するための手段を紹介します。
クエリを直観で作ることが出来るというのはAccessのメリットですが、以下のようなデメリットもあります。
- クエリを作りすぎてしまう
- 何段階もクエリを重ねてしまう
Accessデータベースを構築している時はサクサク作ってしまえるのですが、いざ保守運用する段階になると、手も付けられないといった状態になってしまいます。
この記事を書いている私はAccess歴10年ですが、つい最近までこの問題に悩まされてきました。
ひどい時はテーブルが10個に対しクエリが100個作ってしまうくらいの状態ですので、メンテナンスに必要以上の手間がかかることは言うまでもありません。
しかし、今回紹介するSQLの書き方を意識することにより、作るクエリも５個以下で済み、解読しやすいデータベースを作ることができました。
つまり、９５個はなくせるムダなクエリだったということですよね？
肌感覚ですが、クエリの作り方を見直すことで、保守にかける時間も半分以下で済んだような気がします。
クエリに悩んでいるAccessユーザーにはきっとお役に立てる内容だと思いますので、ぜひご一読ください!。
ちなみに、今回使うサンプルデータベースはこちらからダウンロードできます。
≫購買管理データベース(テーブルのみ)
Accessクエリを減らすために行う2つのテクニック
Accessクエリを減らすためには、SQL文に直接編集する必要があります。
クエリを大幅に減らすためには、本記事で紹介する２つのことをマスターしましょう。
- エイリアスで全体の文字数を減らす
- サブクエリで複数のクエリを一つにまとめる
エイリアスとサブクエリをフル活用すれば、クエリの数を減らす効果もありますが、SQLスキルもかなり鍛えられます。
はじめは、SQL文に直接手を入れることに面倒さを感じてしまいますが、メモ帳ツールなどにコピペしてから編集してみましょう。
原文のSQL文を壊さずに編集できるので、気がねなく作業できます。
一度クエリにひと手間を加える習慣が身についてしまうと、クエリをAccessファイルにため込んでしまう「前の方法」には戻れなくなります。
- AccessフォームのレコードソースにSQL文を設定する
- DAOやADOなどのライブラリでSQL文を使う
サブクエリなどを活用しながら上のに挙げたことを実行すれば、クエリの数を更に減らせることができます。
クエリが減れば、見違えるほど扱いやすいAccessデータベースに生まれ変わります。
エイリアスで文字数を減らす
エイリアスとは「テーブルに別の名前をつける」ということです。
集計クエリなど、関数を使ったフィールドはAS句を使うことになっていますよね？
テーブルの名前も、フィールドと同じようにAS句で新しい名前をつけることができます。
エイリアスは、複数のテーブルをつないで作ったクエリにはとくに有効な方法です。
ここでは、「T01商品マスタ」「T01価格マスタ」「T00小分類マスタ」をつないで「Q商品価格一覧」というクエリをつくってみます。
デザインビューから自動作成されたSQL文は以下のようになります。
SELECT T01部品マスタ.部品登録ID, T01部品マスタ.品名, T00小分類マスタ.小分類名,
T01部品マスタ.型式, T01価格マスタ.価格
FROM (T00小分類マスタ INNER JOIN T01部品マスタ ON T00小分類マスタ.小分類ID = T01部品マスタ.小分類ID)
INNER JOIN T01価格マスタ ON T01部品マスタ.部品登録ID = T01価格マスタ.部品登録ID;
文字数を数えてみると、全部で130文字です。
データシートビューに変換してみると、下のような結果が表示されます。
FROM と JOIN句 以降のテーブル名の後に「AS」を入れ、別のテーブル名を指定します。
テーブル名は「T1」や「T2」など、極力短くしましょう。
そして、ASを入れた箇所以外は命名したテーブル名に変えます。
SELECT T2.部品登録ID, T2.品名, T1.小分類名, T2.型式, T3.価格
FROM (T00小分類マスタ AS T1 INNER JOIN T01部品マスタ AS T2 ON T1.小分類ID = T2.小分類ID)
INNER JOIN T01価格マスタ AS T3 ON T2.部品登録ID = T3.部品登録ID;
エイリアスを使うことで80文字で済ませています。
見直し前の60％程度の文字数で同じ結果を反映できるということです。
このようなちょっとした工夫で文字数を短くし、SQL文を見やすくすることができます。
サブクエリで複数のクエリを一つにまとめる
サブクエリは、SELECT文の中にSELECT文を入れる書き方です。
「副問い合わせ」という言い方もされます。
クエリを重ねる場合は少なくとも2つ以上のSELECT文が必要になりますが、サブクエリを使った場合は１つのSQL文で済ませることができます。
いったんクエリからクエリを作る
ここでは、エイリアスを使って作成したクエリから、更に最安値のみを抽出してみましょう。
集計クエリを使って算出します。
名前は「Q商品最安値」としました。
自動作成されたSQLは下のとおりです。
SELECT Q商品価格一覧.部品登録ID, Q商品価格一覧.品名, Q商品価格一覧.小分類名, Q商品価格一覧.型式, Min(Q商品価格一覧.価格) AS 最安値
FROM Q商品価格一覧 GROUP BY Q商品価格一覧.部品登録ID, Q商品価格一覧.品名, Q商品価格一覧.小分類名, Q商品価格一覧.型式;
JOIN句を使っていませんので、FROM以外のクエリ名を全て省略します。
SELECT 部品登録ID, 品名, 小分類名, 型式, Min(価格) AS 最安値 FROM Q商品価格一覧 GROUP BY 部品登録ID, 品名, 小分類名, 型式;
かなりスッキリしましたね。
データシートビューを確認すると、最安値のデータが表示されているようです。
はじめに作った「商品価格一覧」と比べてみましょう。
商品の重複が解消されているので、「商品最安値」の方がレコード件数が少なくなっているはずです。
2段階のクエリを合わせる
メモ帳などのテキストエディタにSQL文をコピペし、サブクエリを作ってみましょう。
２つ目のクエリの「Q商品価格一覧」の部分を一つ目のSQL文にさしかえるとよいです。
SQL文は以下のようになります。
SELECT 部品登録ID, 品名, 小分類名, 型式, Min(価格) AS 最安値 FROM
(SELECT T2.部品登録ID, T2.品名, T1.小分類名, T2.型式, T3.価格
FROM (T00小分類マスタ AS T1 INNER JOIN T01部品マスタ AS T2 ON T1.小分類ID = T2.小分類ID)
INNER JOIN T01価格マスタ AS T3 ON T2.部品登録ID = T3.部品登録ID)
GROUP BY 部品登録ID, 品名, 小分類名, 型式;
ここで注意しないといけないのは、途中の「;(セミコロン)」を省くということです。
消し忘れるとエラーで先に進めません。
また、作成したサブクエリをデザインビューで確認してみると、「%$##@_Alias」という表記になってしまいます。
デザインビューでは加工前のクエリに戻ることはできませんが、SQLビューやメモ帳などで編集できるので大きな支障はありません。
まとめ
今回は、クエリの数を減らすSQL文の書き方を紹介しました。
クエリの数を削除するためには、エイリアスとサブクエリを思い通りに使えるようになることが必須です。
今回の内容は中上級者向けの内容になってしまいましたが、習得できれば質の高いAccessデータベースを構築できます。
この方法に慣れてしまえば、クエリを重ねる「過去に行っていた方法」には戻れなくなります。
Accessスキルだけでなく、他のデータベースシステムにも通用する考え方だということも、あわせて添えておきます。
次回は、Accessのテーブル構成を変える「データ定義クエリ」の書き方について解説します。