Access: 結合先のテーブル内に条件を満たすレコードが無い行を抽出する(不一致クエリ)

  1. 「社員マスタ」テーブルと「TOEIC受験履歴」テーブルから、TOEICを受験していない社員のデータを抽出したい
  2. 「社員マスタ」テーブルと「TOEIC受験履歴」テーブルから、TOEICで600点以上を取れていない社員のデータを抽出したい

Accessで、上記の例のように、マスタ系のテーブル明細系のテーブルを使って、条件を満たす明細が無いマスタレコードを抽出する場合、不一致クエリに慣れていないと、躓いたり誤ったりしてしまいます。

この記事では、上記のようなデータを抽出するクエリの作り方を、具体例を出しながら説明します。

明細テーブルにデータが存在しないレコードを抽出

例として、社員マスタテーブルとTOEIC受験履歴テーブルから、TOEICを受験していない社員のデータを抽出する方法を紹介します。

使用するテーブルは以下の2つです。

3名の社員が存在することにする
S01(太郎)は1回受験、S03(裕子)は3回受験している。一方で、S02(健司)はTOEICを受験しておらず、データが存在しない。

このデータから、TOEICを受験していない社員を抽出します。
まずは、単純に2つのテーブルを結合(外部結合)します。

2つのテーブルを結合する。[社員マスタ]から[TOEIC受験履歴]へ矢印(→)が伸びればOK。(次の画像を参照)
外部結合になるよう結合プロパティを設定する。(’社員マスタ’は全レコード抽出し、’TOEIC受験履歴’は社員マスタと紐づくレコードだけを抽出する)

クエリを実行すると、下図のようになります。TOEIC受験履歴にデータが無い社員(S02 健司)は、TOEICに関するフィールドがデータ無し(Null)になっています。

この時点では、全社員分のレコードが抽出される。S02(健司)は、TOEIC受験履歴のレコードが無いため、右側はすべて空白(Null)になっている。

TOEIC未受験の社員のみを抽出したいので、TOEIC受験履歴がNullのレコードを抽出します。

TOEIC受験履歴テーブルの社員番号の抽出条件に Is Null を設定。
クエリの実行結果。TOEICの受験履歴がないS02(健司)だけが抽出された。

結合先の明細テーブルにデータが存在しないレコードの抽出方法は以上です。

抽出条件にNullを設定するフィールドは、空白があり得ないフィールド(キー項目など)にしましょう。

たとえば、TOEIC受験履歴テーブルに備考フィールドがあるとして、そこだけに Is Nullの条件を付けてしまうと、受験したけど備考が空白の社員も抽出されてしまいます。

ちなみに、参考までにですが、SQLがわかる場合は、以下のように副問い合わせで実現することもできます。

「TOEIC受験履歴内の社員番号」に含まれない(Not In)という抽出条件。
Not In (SELECT 社員番号 FROM TOEIC受験履歴)
このようにデータが抽出される。

応用:明細テーブルに条件を満たすデータが存在しないレコードを抽出

ここからは応用編です。明細テーブルにデータは有るものの、条件を満たすデータは無いレコードを抽出してみます。

例として、社員マスタテーブルとTOEIC受験履歴テーブルから、TOEICで600点以上のスコアを取れていない社員のデータを抽出する方法を紹介します。

まずは、600点以上のTOEIC受験履歴を抽出する中間クエリを作ります。

[点数]フィールドの抽出条件に >= 600 を設定。
実行結果。TOEIC受験履歴から、600点以上のデータのみが抽出された。

あとは、社員マスタと、このクエリ(TOEIC600点以上)を結合し、先ほどと同様に、TOEIC600点以上の社員番号がNullのレコードを抽出すればOKです。

TOEIC600点以上テーブル(クエリ)の社員番号の抽出条件に Is Null を設定。
クエリの実行結果。TOEICの受験履歴がないS02(健司)に加え、受験履歴はあるが600点以上は取れていないS01(太郎)も抽出された。

以上で、TOEICで600点以上のスコアを取れていない社員のデータを抽出することができました。

ちなみに、参考までにですが、SQLがわかる場合は、以下のように副問い合わせで実現することもできます。

「点数が600点以上のデータの社員番号」に含まれない(Not In)という抽出条件。
Not In (SELECT 社員番号 FROM TOEIC受験履歴 WHERE 点数 >= 600)
このようにデータが抽出される。

コメント