前回は、「Accessのクエリとはどのようなものなのか？」ということについて解説しました。
>>【超入門】Accessクエリとは？全体像について解説します!
今回はクエリ作成機能のひとつ、「クエリウィザード」について解説していきます。
クエリウィザードは、チュートリアルに従ってすすめれば自然とクエリを作ることができる機能です。
Accsssのクエリを作ったことの無い方は、まずは「クエリウィザード」から始めてみることをオススメします。
何度かウィザードでクエリを作っていけば、「こんな感じで作れるんだ」をいうことがつかめます。
Accessの習得に関わる大きなポイントは、データを思い通りに加工できること、つまり「クエリをマスターする」ことです。
この記事を書いている私も、過去に何度もAccessへの挑戦と挫折を繰り返してきました。
「クエリウィザード」を知ることで、クエリのマスターに大きく役立ったのを記憶しています。
そして、Accessの習得に大きく一歩前進できました。
また、この記事を読んでいる方がクエリをさらに使いこなせるよう、ウィザードの説明だけではなく、「デザインビュー」や「SQLビュー」についても触れていますが、全てを理解する必要はありません。
「こんな見方があるんだ」くらいの感覚で、サラッと目を通して頂けるとよいでしょう。
クエリウィザードの基本操作
では、クエリウィザードの大枠の流れについて解説していきます。
まずは、作成タブの「クエリウィザード」を選択します。
ウィザードが起動し、作りたいクエリを選択する画面が表示されます。
4種類のクエリを選ぶことができます。
次にクエリを設定するための画面が表示されますが、作りたいクエリによって設定方法が変わります。
各クエリの詳しい設定については後で解説します。
参照したい項目を選び終わると、クエリの名前をつける画面が表示されます。
最後に「完了」を選択すると、クエリが保存されます。
そして、データシートビューで表示されるので、思った通りにデータが加工されたか確認しましょう。
ウィザードで作ることが出来るクエリ
ここからは、4種類のクエリの作り方について順番に説明していきますね。
選択クエリ
選択クエリは、テーブルの中から見たい項目を選ぶ機能で、クエリの中で最もよく使われる基本的な機能になります。
選択クエリウィザードでは、一つだけではなく、複数のテーブルをつないでデータを並べることができます。
選択クエリウィザードでは、リレーションシップ設定を済ませておかないと複数のテーブルをつなぐことができません。
リレーションシップが設定されていくことを事前に確認しておきましょう。
ここでは、「T00小分類マスタ」の小分類名と「T00大分類マスタ」の大分類名を選択クエリウィザードでつないでみます。
「選択クエリウィザード」を選択すると、「テーブル/クエリ」と「フィールド」を選ぶことができます。
まずは「T00小分類マスタ」の小分類名をフィールドに選びましょう。
「テーブル:T00小分類マスタ」の小分類名を選択し、「＞」をクリックします。
「選択したフィールド」に小分類名が移動しました。
「>>」をクリックすると、全てのフィールドを選ぶことができます。
「<」をクリックすると、対象のフィールドを選択から外すことができます。
次に、「T00大分類マスタ」の大分類名をクエリのフィールドに選択しましょう。
「テーブル/クエリ」のコンボボックスをクリックすると、テーブルやクエリを選ぶことができます。
「T00大分類マスタ」を選択します。
次に「大分類名」を選択し、「＞」をクリックします。
大分類名がフィールドに選択されました。
クエリの結果をデータシートビューで確認してみると、小分類名と大分類名がつながって、一つの表になっていることが分かります。
作ったクエリをデザインビューに切り替えてみます。
クエリのタブを右クリックし、デザインビューを選択します。
デザインビューが表示されました。
ウィザードで選択したフィールドが表示されています。
また、ウィザードで選択していないテーブルも表示されています。
リレーションシップの設定で、大分類と小分類の間に中分類を入れていたためです。
ついでにSQLビューを確認してみます。
クエリタブを右クリックし、「SQLビュー」を選択します。
SQLビューが表示され、SQL文で表記されています。
ここでの説明は割愛しますが、SQLの構文が理解できればSQLビューでの編集も可能です。
参考までにSQL文をコピペできるようにテキストで掲載しておきます。
「SELECT～」と始まる文になっていますが、選択クエリの一番基本となる構文です。
SELECT T00小分類マスタ.小分類名, T00大分類マスタ.大分類名
FROM (T00大分類マスタ INNER JOIN T00中分類マスタ ON T00大分類マスタ.[大分類ID] = T00中分類マスタ.[大分類ID]) INNER JOIN T00小分類マスタ ON T00中分類マスタ.[中分類ID] = T00小分類マスタ.[中分類ID];
ウィザードを完了させる前に「1つまたは複数のテーブルは他と関連がありません」という警告表示がされた場合は、リレーションシップが設定されていません。
リレーションシップ忘れずに設定させておきましょう。
クロス集計クエリ
クロス集計クエリは、Excelのピボットテーブルのような構成になっています。
縦持ちで格納されているテーブルのデータを横持ちに変換することで、データ分析がしやすくなります。
クロス集計を行うため、前もって「集計月」「小分類」「金額」の3つのフィールドをつないだクエリ「Q03発注明細クロス集計前」を作っておきました。
こちらを活用してクロス集計クエリをつくってみます。
「クロス集計クエリ」を作る場合は、1つのクエリからしか作成できません。
ウィザードで「クロス集計クエリ」を選択すると、対象クエリを選択する画面が表示されます。
ここで「Q03発注明細クロス集計前」を選択します。
行見出しの選択画面が表示されますので、「小分類名」を選びます。
列見出しの選択画面が表示されますので、「集計月」を選択します。
値と集計方法を選択する画面が表示されますので、「金額」と「合計」を選択します。
集計方法は、状況によって集計方法を変えましょう。
結果、このように月別の発注実績表ができあがりました。
デザインビューで確認すると、各フィールドに見出しと値が設定されています。
SQLビューを確認してみます。
SQL文のソースコードです。「TRANSFORM～」という書き出しになっています。
TRANSFORM Sum(Q03発注明細クロス集計前.金額) AS 金額の合計
SELECT Q03発注明細クロス集計前.小分類名, Sum(Q03発注明細クロス集計前.金額) AS [合計 金額]
FROM Q03発注明細クロス集計前
GROUP BY Q03発注明細クロス集計前.小分類名
PIVOT Q03発注明細クロス集計前.集計月;
重複クエリ
重複クエリでテーブルの中にデータの重複がないか確認できます。
どちらかというと、誤入力を確認するのに向いています。
ここでは、発注明細の重複データ(2回以上発注している商品)を確認します。
ウィザードで重複クエリを選択した後、クエリを選択します。
重複を確認したいフィールドを選択します。
商品の重複を確認したいので、「品名」を選択しました。
結果に表示したいフィールドを追加します。
ここでは、「希望納期」と「数量」を選択しました。
作成した結果、２つの商品が2回以上発注されていることを確認できました。
デザインビューを確認すると、抽出条件にSELECT文が含まれていることが分かります。
SQLビューを確認します。
SQL文のソースコードです。
SELECT文の中にSELECT文が入れ子になっていますが、このような書き方を「サブクエリ」や「副問い合わせ」と言います。
SELECT Q02発注伝票明細表示用.[品名], Q02発注伝票明細表示用.[希望納期], Q02発注伝票明細表示用.[数量]
FROM Q02発注伝票明細表示用
WHERE (((Q02発注伝票明細表示用.[品名]) In (SELECT [品名] FROM [Q02発注伝票明細表示用] As Tmp GROUP BY [品名] HAVING Count(*)>1 )))
ORDER BY Q02発注伝票明細表示用.[品名];
サブクエリは熟練者が使うテクニックですので、初めのうちは覚える必要はありませんが、こんな書き方があるんだということを知っておくと後で役に立ちます。
不一致クエリ
不一致クエリは、2つのテーブル間での照合を行うクエリです。
不一致クエリウィザードでは、2つのテーブル間のどのフィールドを照合するかを設定します。
ここでは「部品マスタ」と「発注伝票明細」の不一致、つまり一度も発注されていない部品を調べます。
こちらが部品マスタのテーブルです。ウィザードで品名を選択します。
こちらは発注伝票明細のテーブルですが、同じく品名を選択します。
不一致クエリウィザードでは、レコードを抽出するテーブル(データが多い方)を先に選びます。
ここでは、「T01部品マスタ」になります。
次に、比較するテーブル(データが少ない方)を選択します。
ここでは、「T02発注伝票明細」になります。
フィールドを選択する画面が表示されますので、どちらも「品名」を選択して「<=>」をクリックします。
「関連付けるフィールド」に品名が設定されていることが分かります。
結果に表示するフィールドを選択します。ここでは全てのフィールドを選択しました。
クエリを作成した結果、未発注のデータが6件あることが分かりました。
デザインビューはこのようになっています。
不一致クエリの特長として、
- テーブル同士の結合が矢印になっている
- 比較側のフィールドが「IsNull」になっている
なぜそのような設定になっているのかは、後日詳しく解説します。
SQLビューを確認してみます。
SQLビューのソースコードです。
SELECT T01部品マスタ.部品登録ID, T01部品マスタ.品名, T01部品マスタ.型式, T01部品マスタ.規格, T01部品マスタ.[サイズ], T01部品マスタ.小分類ID, T01部品マスタ.備考, T01部品マスタ.伝票表示
FROM T01部品マスタ LEFT JOIN T02発注伝票明細 ON T01部品マスタ.[品名] = T02発注伝票明細.[品名]
WHERE (((T02発注伝票明細.品名) Is Null));
まとめ
このようにして、クエリウィザードを使うことで4種類のクエリを作ることができます。
クエリウィザードを使うことで、今までクエリを全く作ったことの無い方でもチュートリアルに沿ってクエリを作ることができます。
ウィザードでクエリを作るのに慣れてきたら、デザインビューからのクエリ作りに挑戦してみましょう。
選択クエリは、デザインビューを活用することで、選択クエリウィザードよりも細かくカスタマイズできます。
更に、クロス集計クエリについても、デザインビューから作ることはそんなに難しくないので、慣れればスムーズに習得できます。
「重複クエリ」と「不一致クエリ」の2種類をデザインビューでイチからつくるのは難易度が高いです。
Access中級者以上の方にとっても、「重複クエリと不一致クエリを簡単に作ることができること」はクエリウィザードを使う大きなメリットとも言えるでしょう。
今後も、Accessのクエリについての解説を進めますが、次回は「デザインビュー」の基本的な使い方について解説します。
≫ 【入門】Accessクエリをデザインビューで操作する