■ 注文件数が６件以上の得意先を抽出する (2000, 2002, 2003,2007)
得意先テーブルと受注テーブルから注文件数が６件以上の得意先を抽出するクエリを作成します。受注テーブルから注文件数が６件以上の得意先IDを抽出するには、GROUP BY句とHAVING句を使用します。
1. 新規データベース作成
Accessを起動したら、ツールバーから新規作成のアイコン
をクリックして、作業フォルダworkに新規データベース077.mdbを作成します。
2. テーブルをインポート
メニューバーから[ファイル]→[外部データの取り込み]→[インポート]を選択します。「インポート」のダイアログが表示されたら、077.mdb(このページの最後からダウンロードできます)を選択して[インポート]ボタンをクリックします。
「オブジェクトのインポート」が表示されたら、[テーブル]タブから[tbl得意先]と[tbl受注]をクリックして選択します。[OK]ボタンをクリックしてインポートを開始します。インポートが完了すると、データベースウィンドウにインポートしたテーブルが表示されます。
3. 新規クエリ作成
データベースウィンドウのオブジェクトから[クエリ]をクリックして選択したら、新規オブジェクトのショートカットから[デザインビューでクエリを作成する]をダブルクリックします。「テーブルの表示」ダイアログが表示されたら、何も選択しないで[閉じる]ボタンをクリックしてダイアログを閉じます。
クエリのデザインビューが表示されたら、ツールバーからSQLビューのアイコン
をクリックします。SQLビューが表示されたら、次のSQLステートメントを入力します。
SELECT tbl得意先.得意先ID, tbl得意先.得意先名
FROM tbl得意先
WHERE tbl得意先.得意先ID IN
(SELECT tbl受注.得意先ID FROM tbl受注
GROUP BY tbl受注.得意先ID HAVING COUNT(*) > 6);
ツールバーから上書き保存のアイコン
をクリックしたら「qry注文件数が６件以上の得意先を抽出」の名称で保存します。
図 新規クエリを作成したら「qry注文件数が6件以上の得意先を抽出」の名称で保存
4. サブクエリ実行
ツールバーから実行のアイコン
をクリックして、サブクエリを実行します。データシートに過去３年間注文がなかった得意先が１件表示されます。
図 注文件数が６件以上の得意先を表示する
●解説
受注テーブルから注文件数が６件以上の得意先IDを抽出するには、GROUP BY句で得意先IDを集計してHAVING句にCOUNT(*) > 6の抽出条件を設定します。
SELECT tbl受注.得意先ID FROM tbl受注
GROUP
BY tbl受注.得意先ID
HAVING
COUNT(*) > 6
得意先テーブルからこのクエリに含まれている得意先を抽出するには、INを使用して次のようなWHERE句を作成します。
WHERE
tbl得意先.得意先ID IN
(SELECT
tbl受注.得意先ID FROM tbl受注 GROUP BY tbl受注.得意先ID HAVING COUNT(*) > 6)
WHERE句の得意先ID IN (SELECT...)は、サブクエリに含まれている得意先を抽出することを意味します。後は、親のSELECTステートメントにこのWHERE句を追加すると、目的のクエリが完成します。
完成したサブクエリ：
SELECT tbl得意先.得意先ID, tbl得意先.得意先名
FROM tbl得意先
WHERE tbl得意先.得意先ID IN
(SELECT
tbl受注.得意先ID FROM tbl受注 GROUP BY tbl受注.得意先ID HAVING COUNT(*) > 6);
Tip
|
得意先が３件以上の都道府県を表示する ここで作成したサブクエリを応用すると、得意先の件数が３件以上の都道府県を表示させることができます。サブクエリには、GROUP BY句とHAVING句を追加して得意先の件数が３件以上の都道府県を抽出します。親のWHERE句にINと組み合わせたサブクエリを追加すると、目的のクエリが完成します。都道府県を重複させたくないときは、親のクエリにDISTINCTを追加します。 SELECT DISTINCT tbl得意先.都道府県 FROM tbl得意先 WHERE (((tbl得意先.都道府県) IN (SELECT
都道府県 FROM
[tbl得意先] GROUP BY 都道府県 HAVING
Count(*)>3 ))); |
Tip
|
フィールド値が重複するレコードを見つける ここで作成したクエリを応用すると、得意先テーブルの得意先、担当者名、電話番号などが重複するレコードを抽出することができます。また、既にレコードが登録されているテーブルに主キーを設定するとき、主キーがユニークかどうか調べることができます。 たとえば、得意先重複有のテーブルから得意先名が重複しているレコードを抽出するには、サブクエリにGROUP BY句とHAVING句を追加します。GROUP BY句には、得意先名、HAVING句には、COUNT(*) > 1を指定します。 SELECT 得意先ID, 得意先名 FROM tbl得意先重複有 WHERE 得意先名 IN (SELECT 得意先名 FROM tbl得意先重複有 GROUP BY 得意先名 HAVING COUNT(*)>1); |