Access

Access【クエリ】不一致/重複/クロス/追加/削除/ユニオン/関数徹底解説④

スポンサーリンク

Access徹底解説④ではクエリの中から、選択/不一致/重複/クロス集計クエリ、アクションクエリから追加/削除/更新/テーブル作成クエリ、SQLクエリからユニオンクエリなどについて詳しくご紹介していきます。

 

1.クエリの基礎

 

(1)クエリとは

クエリとは、単一または複数のテーブルから、必要なフィールドだけを取り出して、1つの「仮想テーブル」を作成することができるオブジェクトです。

クエリは「仮想テーブル」ですので、クエリ自体はデータは持たず、テーブルのデータを参照して表示します。

ですから、クエリを作成するにはテーブルが必要になります。

クエリと他のオブジェクトの関係は次のようになります。

Access徹底解説クエリ

 

 

(2)クエリの種類

クエリの種類は、大きく分けて4つに分類されます。

必要なフィールドを選択して作る「選択クエリ」がもっとも基本のクエリになります。

次の表のように、それぞれクエリの種類によって、作成方法が異なります。

クエリの種類 作成方法
■選択クエリ 選択クエリ テーブルデザイン / ウィザード
重複クエリ ウィザード
不一致クエリ
■クロス集計クエリ
■アクションクエリ 追加クエリ 選択クエリを作成してから目的のクエリに編集
更新クエリ
削除クエリ
テーブル作成クエリ
■SQLクエリ ユニオンクエリ SQLビューで作成
パススルークエリ
データ定義クエリ
サブクエリ

 

 

2.選択クエリの作成

 

(1)選択クエリ

「選択クエリ」とは、テーブルから必要なフィールドだけを選択して組み合わせたクエリです。

もっとも基本的な構造をもち、通常「クエリ」といえば、選択クエリを指します。

選択クエリでは、並べ替えや抽出、演算フィールドや集計、関数、パラメータクエリなど様々な加工や分析ができます。

また、アクションクエリやSQLクエリを作成する場合も、選択クエリを元にして加工していきます。

選択クエリの作成方法は、「クエリデザイン」、「ウィザード」の2つの方法がありますので、それぞれ確認していきます。

 

今回は「T売上データ」「T商品マスター」「T顧客マスター(東京)」から次の「Q売上データ集計表」クエリを作成してみます。

■作成するクエリ

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

■使用するテーブル

<T売上データ>

<T商品マスター>

<T顧客マスター(東京)>

 

ポイント

クエリは、単一テーブルからでも複数のテーブルからでも作成できますが、複数テーブルから作成する場合、テーブル間にリレーションシップの設定がされている必要があります。↓↓

Accessリレーションシップ

 

※リレーションシップの設定について詳しくみたい場合「Access【リレーションシップ】一対多/参照整合性/連鎖更新など徹底解説③」↓↓

Accessリレーションシップ
参考Access【リレーションシップ】一対多/参照整合性/連鎖更新など徹底解説③

スポンサーリンク   Accessはリレーショナルデータベースソフトです。 Accessでテーブルを作成したら、次はテーブル間に「リレーションシップ」の設定をしていきましょう。 リレーションシップの設 ...

続きを見る

 

 

◆選択クエリを《クエリデザイン》で作成する

 

①《作成》タブー《クエリ》グループー《クエリデザイン》をクリック。

 

②《テーブルの表示》ー3つのテーブルを追加。

 

③必要なフィールドをデザイングリッドに追加。

フィールドが追加されました。

 

④「顧客CD」の右側に「氏名」フィールドを挿入。

 

⑤「商品CD」の右側に「商品名」「単価」フィールドを挿入→

 《デザイン》タブー《結果》グループー《表示》をクリック。

Access徹底解説

結果が表示されます。

※「T売上データ」テーブルにはなかった「氏名」や「商品名」「単価」などが表示されました。

 それぞれの「CD」を元にして、関連付けた他のテーブルからフィールド引き出され、情報だけが表示されています。

Access徹底解説選択クエリ    

 

⑥クエリに名前を付けて保存。

 

 

 

◆選択クエリを「ウィザード」で作成する方法

上と同じクエリをウィザードで作成する方法をご紹介します。

 

①《作成》タブー《クエリ》グループー《クエリウィザード》をクリック。

 

②《新しいクエリ》ー《選択クエリウィザード》をクリックー《OK》

 

③必要なテーブル「T売上データ」を選択して、>>をクリック。

全てのフィールドが右側に移動して、選択されます。

Access徹底解説クエリ

 

④さらに、フィールドを追加します。

《選択したフィールド》から「顧客CD」を選択しておき、「T顧客マスター(東京)」を選び「氏名」を選択したら >をクリック。

「氏名」フィールドが《選択したフィールド》に追加されました。

 

⑤さらに、「T商品マスター」から「商品名」「単価」を選択→次へ。

 選択したフィールドの下側にフィールドは追加されます。

 

⑥今回は、集計せずに《次へ》。

 

⑦クエリに名前を付けて保存。

      

選択クエリでは、レコードの並べ替え、抽出、パラメータクエリ、演算フィールド、集計などができます。

※並べ替え、演算フィールドについて詳しくみる場合は、「アクセス2019 使い方⑤ 初心者向け クエリの作成 並べ替え 演算フィールド」をご覧ください↓↓

こちらもCHECK

Accessクエリ作成
アクセス2019 使い方⑤ 初心者向け クエリの作成 並べ替え 演算フィールド

スポンサーリンク アクセスの使い方⑤では、アクセスの使い方①~③で作成したテーブルを元に必要なフィールドだけを登録して新たな仮想テーブルを作成していきます。 複数のテーブルからそれぞれのフィールドを登 ...

続きを見る

※BetweenAnd、パラメータクエリ、集計について詳しくみる場合は、「アクセス2019 使い方⑤ 初心者向け クエリの作成 並べ替え 演算フィールド」をご覧ください↓↓

こちらもCHECK

Accessクエリ
アクセス2019 使い方⑥クエリの作成 Between And パラメータ 集計

スポンサーリンク アクセスの使い方⑤では、クエリの作成方法を説明しました。 アクセスの使い方⑥では、クエリにさまざまな条件を設定して、条件にあてはまるレコードを抽出する方法とクエリを集計する方法を説明 ...

続きを見る

 

(2)重複クエリウィザード

「重複クエリ」は、1つのテーブルやクエリの、指定したフィールドに、同じ(重複)データが入力されていた場合に、その重複データが含まれているレコードだけを表示するクエリです。

例えば、同じ商品名や人名を二重に登録していないかなどを調べることができます。

 

ポイント

今回は「Q売上データ集計表」から、重複している「顧客CD」フィールドを持つレコードを表示して、リピーターの顧客を調べてみます。

 

①《作成》タブー《クエリ》グループー《クエリウィザード》をクリック。

 

②《新しいクエリ》ー《重複クエリウィザード》ー《OK》をクリック。

 

重複データを調べるクエリ「クエリ:Q売上データ集計表」をクリックして、次へ。

 

重複データを調べるフィールド「顧客CD」選択して、> をクリック。

「顧客CD」が《選択したフィールド》に移動したら、次へ。

 

クエリの結果に表示したいフィールド「売上日」「氏名」を 選んで、>をクリック。

「顧客CD」が《選択したフィールド》に移動したら、次へ。

 

⑥クエリの名前を指定して、《完了》。

 

<結果>

オブジェクトに「重複クエリ」が表示され、結果が表示されました。

 「顧客CD」の「kobbb」が重複していることが分かりました。

 よって、「戸部さん」がリピーターの顧客ということが分かりました。

Access徹底解説クエリ 

 

 

(3)不一致クエリウィザード

「不一致クエリ」は、2つのテーブルやクエリを比較して、指定したフィールドのデータが、片方のテーブルやクエリのみに入力されていて、2つのテーブル間で一致しないデータが含まれているレコードだけを表示するクエリです。

例えば、商品名が入力されている商品マスターテーブルと、売上データのテーブルを比較して、売上データテーブルにない商品名を表示すると、売上のない商品を調べることができます。

今回は、「T顧客マスター」と「T売上データ」「顧客CD」のフィールドを比較して、売上実績のない顧客を調べてみます。

<T顧客マスター>

<売上データ>

Access徹底解説クエリ

 

①《作成》タブー《クエリ》グループー《クエリウィザード》をクリック。

 

②《新しいクエリ》ー《不一致クエリウィザード》ー《OK》をクリック。

 

③《不一致クエリウィザード》ーレコードを抽出するテーブル/クエリは「T顧客マスター(東京)」を選択ー《次へ》。

 ※抽出結果には、ここで指定したテーブル/クエリのフィールド名を選択することができます。

 

比較に使うテーブル/クエリは「T売上データ」を選択ー《次へ》。

 

⑤2つのテーブル/クエリのフィールド一覧が左右に表示されます。

 不一致を調べる(関連付ける)フィールド「顧客CD」を選択して <=> をクリックー《次へ》。

 

⑥1つめに選択したレコードを抽出するテーブル/クエリの「T顧客マスター」のフィールド一覧が表示されるので、クエリの結果に表示するフィールドを選択、全て表示する場合は  >> をクリックして《次へ》。

 

⑦不一致クエリの名前を指定ー《完了》。

 

<結果>

オブジェクトに「不一致クエリ」が表示されたので、ダブルクリックで実行すると、結果が表示されました。

 「顧客CD」の「koddd」と「koeee」の販売実績ないことが分かりました。

Access徹底解説クエリ

 

    

 

3.クロス集計クエリ

 

「クロス集計クエリ」は、1つのテーブルやクエリから必要なフィールドの「行見出し」や「列見出し」を指定し、それらが交差するエリアに集計を求め、「クロス集計表」を作成できます。

例えば、行見出しに「支店名」フィールド、列見出しに「売上日」フィールドを指定し、交差するエリアに集計結果を表示できます。

※もし、2つ以上のテーブルからフィールドを選択したい場合には、予め必要なフィールドを含めたクエリを作成しておきます。

 

今回は、「T売上データ」テーブルを元に、行見出しに「売上日」フィールド、列見出しに「商品CD」フィールドを指定し、交差するエリアに、売上日/商品CDごとの数量を集計するクロス集計表を作成してみます。

 

①《作成》タブー《クエリ》グループー《クエリウィザード》をクリック。

 

②《新しいクエリ》ー《クロス集計クエリウィザード》ー《OK》をクリック。

 

③《クロス集計クエリウィザード》ークロス集計をするテーブル/クエリは「T売上データ」を選択ー《次へ》。

 

行見出しとして使うフィールドを「売上日」を選択して > をクリックー《次へ》。

 

列見出しとして使うフィールドを「商品CD」を選択して > をクリックー《次へ》。

 

集計するフィールド「数量」と集計方法「合計」を選択ー《次へ》。

 

⑦クロス集計クエリに名前を指定して《完了》。

 

<結果>

オブジェクトに「クロス集計クエリ」が表示されたので、ダブルクリックで実行すると、結果が表示されました。

 数量が合計され、集計表が表示されました。

Access徹底解説クエリ      

 

 

4.アクションクエリ

アクションクエリは、テーブルのデータに直接変更を加えるクエリです。

テーブルから、指定したレコードをコピーして新たなテーブルを作成する「テーブル作成クエリ」、テーブルのデータをいっきに更新する「更新クエリ」、テーブルの指定したレコードを削除する「削除クエリ」、指定したテーブルにレコードを追加する「追加クエリ」の4種類があります。

「T顧客マスター(東京)」を使用して、4つのアクションクエリを確認してみます。

Access徹底解説クエリ

 

(1)テーブル作成クエリ

テーブル作成クエリで、「T顧客マスター(東京)」の退会者を新規テーブルにコピーしてみます。

①《作成》タブー《クエリ》グループー《クエリデザイン》をクリック。

 

②《テーブルの表示》ー「T顧客マスター(東京)」を追加。

 

③フィールドリストから全てのフィールドをデザイングリッドに挿入

「退会」フィールドの抽出条件に「Yes」と入力ー《デザイン》タブー《クエリの種類》ー《テーブルの作成》をクリック。

 

④新しいテーブルの名前を入力。

 

⑤テーブル作成クエリの名前を入力。

ポイント

オブジェクトにテーブル作成クエリが作成されました。

アクションクエリを作成すると、クエリに「!」のマークが表示されます。

ですから、「!」が付いているクエリは、テーブルに直接変更を加えてしまうクエリという意味です。

実行すると、確認のメッセージが表示されるので、よく読んでから実行してください。

クエリはまだ実行されていないので、クエリをダブルクリックすると、アクションクエリが実行されて、テーブルが作成されます。

 

テーブル作成クエリをダブルクリックして実行

 

⑦メッセージが表示されたら「はい」をクリック。

 

<結果>

オブジェクトに、「T顧客マスター(退会者リスト)」が作成されました。

 開くと、退会者のレコードがコピーされました。

 

注意ポイント

テーブル作成クエリを実行すると、レコードがコピーされます。

移動されたわけではないので、データは、元のテーブルに残っています。

必要に応じて、削除しましょう。

 

 

(2)削除クエリ

削除クエリを利用して、「T顧客マスター(東京)」の退会者を削除します。

 

①《作成》タブー《クエリ》グループー《クエリデザイン》をクリック。

 

②《テーブルの表示》ー「T顧客マスター(東京)」を追加。

 

③フィールドリストから全てのフィールドをデザイングリッドに挿入→

「退会」フィールドの抽出条件に「Yes」と入力ー《デザイン》タブー《クエリの種類》ー《削除》をクリック。

 

クエリに名前を付けて保存。

オブジェクトに「削除クエリ」が作成されました。

 

⑤「削除クエリ」をダブルクリックで実行→メッセージが表示されたら「はい」をクリック。

<結果>

「kofff」のレコードが削除されました。

 

 

(3)追加クエリ

追加クエリを利用して、「T顧客マスター(東京)」の退会者を「T退会者リスト」に追加してみます。

 

事前に、1件のデータの退会に☑をつけておくので、これを「T退会者リスト」に追加します。

 

①《作成》タブー《クエリ》グループー《クエリデザイン》をクリック。

②《テーブルの表示》ー「T顧客マスター(東京)」を追加。

③フィールドリストから全てのフィールドをデザイングリッドに挿入→

 「退会」フィールドの抽出条件に「Yes」と入力ー《デザイン》タブー《クエリの種類》ー《追加》をクリック。

 

追加するテーブルを指定します。

 

クエリに名前を付けて保存します。

⑥オブジェクトに「追加クエリ」が作成されたので、ダブルクリックして実行。

 メッセージが表示されたら「はい」をクリック。

 

<結果>

テーブルを開いて確認すると、退会者が追加されて、2件になりました。

 

参考

削除クエリを実行して、「T顧客マスター(東京)」から「退会者」を削除しておきます。

 

 

(4)更新クエリ

更新クエリを利用して、「T顧客マスター」のDMを全てオフに更新してみます。

 

①《作成》タブー《クエリ》グループー《クエリデザイン》をクリック。

②《テーブルの表示》ー「T顧客マスター(東京)」を追加。

③フィールドリストから全てのフィールドをデザイングリッドに挿入→

《デザイン》タブー《クエリの種類》ー《更新》をクリックー「DM」フィールドの《レコードの更新》に「No」と入力。

※全てのレコードを更新したいので、抽出条件は設定しない。

 

クエリに名前を付けて保存

⑤オブジェクトに「更新クエリ」が作成されるので、「更新クエリ」をダブルクリックして実行。

 メッセージが表示されたら「はい」をクリック。

<結果>

DMフィールドが全てオブになりました。

 

さらに、「T顧客マスター」のDMを日付を指定して、オンに更新する

①《作成》タブー《クエリ》グループー《クエリデザイン》をクリック。

②《テーブルの表示》ー「T顧客マスター(東京)」を追加。

③フィールドリストから全てのフィールドをデザイングリッドに挿入→

 デザイングリッドの《抽出条件》に「between 2021/8/1 and 2021/8/31」と入力してEnter。(#は自動的に表示される)→

 《デザイン》タブー《クエリの種類》ー《更新》をクリックー「DM」フィールドの《レコードの更新》に「Yes」と入力。

 

クエリに名前を付けて保存

 

⑤オブジェクトに更新クエリが表示されるので、更新クエリをダブルクリックして実行。

 メッセージが表示されたら「はい」をクリック。

<結果>

DMフィールドの8月のレコードに☑が入りました。

Access徹底解説クエリ        

                         

 

5.SQLクエリ

(1)ユニオンクエリ

「ユニオンクエリ」は、複数のテーブルやクエリのデータを同じフィールドなどで、1つのクエリにまとめて抽出し、結合することができます。

例えば、東京の社員名簿テーブルと大阪の社員名簿テーブルの各フィールドを取り出し、結合して表示できます。

ユニオンクエリを作成するには、まず選択クエリで目的のフィールドを抽出して作成し、次にSQLビューに切り替えて、SQL文を編集します。

今回は、次の2つのテーブルをユニオンクエリで結合してみます。

※ユニオンクエリでテーブルを結合するには、2つのテーブルの構造が同じ必要があります。

<T顧客マスター(東京)>

<T顧客マスター(大阪)>

Access徹底解説ユニオンクエリ

①《作成》タブー《クエリ》グループー《クエリデザイン》をクリック。

②《テーブルの表示》ー「T顧客マスター(東京)」を追加→

 フィールドリストの「*」をドラッグすると、全てのフィールドが追加されます。→

 《デザイン》タブー《結果》グループー《SQLビュー》をクリック。

 

結合したいテーブルを指定するために、1~2行目を下にコピーする。

 

④次のように編集します。

 2行目の「;」を削除→「Union」と入力→「大阪」に修正

ポイント

ユニオンクエリの構文

  • SELECT → 選択フィールド名
  • FROM → 選択テーブル/クエリ名
  • UNION
  • SELECT → 結合フィールド名
  • FROM → 結合テーブル/クエリ名

 

⑤《デザイン》タブー《結果》グループー《実行》をクリック。

<結果>

2つのテーブルが結合されました。

 

⑥クエリに名前を付けて保存します。

Access徹底解説クエリユニオンクエリ      

 

参考

テーブルの結合は、追加クエリでも実行できすますが、結合するテーブルの数が多い場合には、ユニオンクエリの方が便利です。

 

6.関数

Accessでも関数を使うことができますが、Excelとは使い方が違います。

※Accessでの関数について詳しくみたい場合は「アクセス2019 使い方⑦クエリ Month DateDiff Right IIf関数]

もご覧ください↓↓

こちらもCHECK

Accessクエリー
アクセス2019 使い方⑦クエリ Month DateDiff Right IIf関数

スポンサーリンク アクセスの使い方⑦では、関数を使ったクエリの作成方法を説明していきます。 関数を利用して演算フィールドを作成したり、必要なデータを抽出したりする方法を解説します。 関数はExcel経 ...

続きを見る

 

sangoya
最後までお読みいただきありがとうございました。

Accessを使いこなすうえで、クエリは最も重要なオブジェクトです。

できることが、多いため紹介しきれませんでしたが、随時更新していきます。

 

 

 

 

 

スポンサーリンク

-Access

© 2021 無料オンラインパソコンスクール-SANGOYA Powered by AFFINGER5