Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Access 2010)
複数テーブルの不一致クエリ
投稿日時: 19/08/04 14:16:22
投稿者: 忠犬

はじめまして。アクセス初心者のため四苦八苦しています。
テーブルが4つあり、「01元データ」のうち、「02照会記録」、「03電話記録」、「04回答記録」のいずれのテーブルにもデータが無いものを不一致クエリで抽出したいのですがどう書いたらいいのか頭を抱えています。
最終的に「01元データ」で ID 1、ID 2のデータを表示したいです。お力をお貸しください。
 
テーブル名:01元データ
ID 会社番号 顧客番号 顧客氏名
1  5256   25   あいう えお
2  6532  986   かきく けこ
3   985   6   さしす せそ
 
テーブル名:02照会記録
ID 会社番号 顧客番号 氏名
1  3621   7432  なにぬ ねの
2  5256  25  あいう えお
 
テーブル名:03電話記録
ID 会社番号 顧客番号 氏名
1  985     6  さしす ねの
2  8425    254  はひふ へほ
 
テーブル名:04回答記録
ID 会社番号 顧客番号 顧客氏名
1   985    6  さしす せそ

回答
投稿日時: 19/08/04 20:10:17
投稿者: HAL

色々やり方はありますがサブクエリで!!
 
SELECT ID,会社,顧客番号,顧客指名 FROM 01元データ
WHERE 会社番号 NOT IN(
SELECT 会社番号 FROM 02照会記録
UNION
SELECT 会社番号 FROM 03電話記録
UNION
SELECT 会社番号 FROM 04回答記録);

回答
投稿日時: 19/08/04 20:32:31
投稿者: HAL

会社でなく会社番号でしたね。
下記に修正します。
 
01データで02・03・04のTABLEデータがないものと解釈しての回答です。
 
 
SELECT [01元データ].ID, [01元データ].会社番号, [01元データ].顧客番号, [01元データ].顧客氏名 
FROM 01元データ
WHERE ((([01元データ].[会社番号]) Not In (SELECT 会社番号 FROM
(
SELECT 会社番号 FROM 02照会記録
UNION
SELECT 会社番号 FROM 03電話記録
UNION
SELECT 会社番号 FROM 04回答記録) AS 1)));
 
 
データ量が多いと重くなります。

投稿日時: 19/08/05 05:21:17
投稿者: 忠犬

おはようございます。
HALさん、早速回答ありがとうございます。
 
会社番号と顧客番号が同じでも、氏名が異なるケースがあるため、それは01元データに表示したかったのですが、特に会社番号+顧客番号+氏名とつなげなくても良いのですね!!
今日会社で試してみようと思います!

回答
投稿日時: 19/08/05 22:53:25
投稿者: mayu.

引用:
会社番号と顧客番号が同じでも、氏名が異なるケースがある

実務をこなす前に
データベース設計、特に「 正規化 」の概念は
何が何でも理解するよう 勉強することを強くお薦めします。
https://www.accessdbstudy.net/archive/category/%E6%AD%A3%E8%A6%8F%E5%8C%96
 
2019/08/04 14:16:22 に提示いただいたデータ例のように
顧客情報がいろんなテーブルに散在している状態は
一事実複数箇所と言い、設計ミスの部類になりますから
これを正して
 
  一事実一箇所( 1 fact in 1 place )
 
になるよう設計すれば、こういった質問自体しなくても済みますし、
クエリの作り方も簡単且つ高速になります。
 
また、クエリ1つで実現しようとなさらずに
会社番号, 顧客番号, 顧客氏名 を格納できる 空の一時テーブルを作成し、
空のテーブルへ
 
 1. 追加クエリで 02照会記録 のデータを追加
 2. 追加クエリで 03電話記録 のデータを追加
 3. 追加クエリで 04回答記録 のデータを追加
 
した後、01元データ テーブルと 一時テーブルのデータを
不一致クエリで比較するといった手順でしたら
多少、手間と時間はかかるでしょうけど
抽出の仕組みは 理解しやすいのではないかと思います。
 
引用:
会社番号と顧客番号が同じでも、氏名が異なるケースがあるため、
それは01元データに表示したかったのですが、
特に会社番号+顧客番号+氏名とつなげなくても良いのですね!!

いえ。良くありません。
こういった場合は
   会社番号 + 顧客番号 + 氏名 で一意のデータになる
とか
   会社番号 + 顧客番号 + 氏名 で固有の値になる
という表現を用い、
3つのフィールド全てで比較をおこなう必要があります。
(
  各フィールドの値を結合し、一つの値として比較する方法は
  桁揃えの作業が必要になり、インデックスも使えなくなるため
  複数フィールドの比較方法としては実行効率・見通しともに悪いです
)
設計の観点から申し上げるなら
本来は、顧客番号 のみを主キーとする
( 顧客情報だけを網羅した )マスタテーブルが存在していなければなりませんし
照会・電話・回答というアクションに対しても
場合によっては一つのテーブルで管理できるでしょう。
 
  < 例 >
  
  作業ID  顧客番号   日付   アクション種別    内容
  -----------------------------------------------------------------------
    1    123   2019/04/09    照会     契約内容
    2    123   2019/06/10    電話    商品へのクレーム
    3    123   2019/06/22    回答    故障による機能不全
    4    999   2019/06/23    電話    支払方法の変更手順
    5    653   2019/07/11    照会     取引履歴

どうしても 現状の構成で解決しなければならないのでしたら
以下のようなSQLで、ご希望の結果になるでしょう。
 
SELECT Max( q.ID ) As ID
     , 会社番号
     , 顧客番号
     , 顧客氏名
FROM 
(
    SELECT ID, 会社番号, 顧客番号, 顧客氏名, 1 As flag
    FROM 01元データ
    UNION ALL
    SELECT 0,  会社番号, 顧客番号, 氏名,     2
    FROM 02照会記録
    UNION ALL
    SELECT 0,  会社番号, 顧客番号, 氏名,     3
    FROM 03電話記録
    UNION ALL
    SELECT 0,  会社番号, 顧客番号, 顧客氏名, 4
    FROM 04回答記録
) q
GROUP BY 会社番号
       , 顧客番号
       , 顧客氏名
HAVING Max( flag ) = 1
ORDER BY Max( q.ID ) ;

投稿日時: 19/08/05 23:10:36
投稿者: 忠犬

HALさん、今日会社で試してみましたが、エラーメッセージが表示され、動かすことができませんでした。
エラー内容はを記載しようと持ち帰る予定をしていましたが忘れてしまいました。('()'がなんとか…すみません。)
 
mayuさん、回答ありがとうございます。
職場でネットが閲覧できないため、遅くなりました。
後出しにはなりますが、複数テーブルで管理するより、他の3つは統合して1つのテーブルにし、元データテーブルと比較する方が良いと思ったのですが、諸事情によりこの方法でいくことしかないできないのです。
 
また、今回質問した内容は、実際のフィールド名とは異なりますが構造は同じで、この会社番号と顧客番号は個人を特定するためにどうしても必要な項目であり変更することはできません。
ご指摘はごもっともだと思っています。ありがとうございます。
明日試してみます!!

投稿日時: 19/08/06 12:29:54
投稿者: 忠犬

mayuさん、求めていた結果が出ました!
ありがとうございます。
これに関連して、01元データにある他のフィールドも表示したいのですが、どこに表記したら良いでしょうか?
(むりやり最初のselectのところにフィールド名をいれたらパラメーターを求められ、キャンセルするとexpr001 というフィールドが出現(値は0)してしまい焦っています。)
後出しばかりですみませんが、これで最後ですのでよろしくお願いします!

回答
投稿日時: 19/08/06 20:22:47
投稿者: mayu.

引用:
01元データにある他のフィールドも表示したいのですが、
どこに表記したら良いでしょうか?

IDフィールドはオートナンバー型だとして
 
SELECT x.*
FROM 01元データ x
INNER JOIN
(
    SELECT Max( q.ID ) As ID
    FROM 
    (
        SELECT ID, 会社番号, 顧客番号, 顧客氏名, 1 As flag
        FROM 01元データ
        UNION ALL
        SELECT 0,  会社番号, 顧客番号, 氏名,     2
        FROM 02照会記録
        UNION ALL
        SELECT 0,  会社番号, 顧客番号, 氏名,     3
        FROM 03電話記録
        UNION ALL
        SELECT 0,  会社番号, 顧客番号, 顧客氏名, 4
        FROM 04回答記録
    ) q
    GROUP BY 会社番号
           , 顧客番号
           , 顧客氏名
    HAVING Max( flag ) = 1
) y
ON x.ID = y.ID
ORDER BY x.ID ;

或いは
 
SELECT x.*
FROM 01元データ x
LEFT JOIN
(
    SELECT 会社番号, 顧客番号, 氏名 As 顧客氏名
    FROM 02照会記録
    UNION ALL
    SELECT 会社番号, 顧客番号, 氏名
    FROM 03電話記録
    UNION ALL
    SELECT 会社番号, 顧客番号, 顧客氏名
    FROM 04回答記録
) y
ON x.会社番号 = y.会社番号 AND
   x.顧客番号 = y.顧客番号 AND
   x.顧客氏名 = y.顧客氏名
WHERE y.会社番号 Is Null
ORDER BY x.ID ;

投稿日時: 19/08/07 19:12:19
投稿者: 忠犬

mayuさん、度々ありがとうございます!
今日会社で試してみましたが、from 句の構文エラーです、と出てしまいMax( q.ID ) の最初のカッコ部分にカーソル表示されます。
ならばとお示しいただいた2つ目を試したところ、今度はUNIONでのエラーが表示されました。
今日は作業時間がうまく取れず、このエラーは私の書き方が悪いせいだと思うので、明日もう1度やってみてご報告します

回答
投稿日時: 19/08/07 21:07:54
投稿者: mayu.

引用:
今日会社で試してみましたが、from 句の構文エラーです、と出てしまい
Max( q.ID ) の最初のカッコ部分にカーソル表示されます。

両方のSQLとも、2019/08/04 14:16:22 に
忠犬さんが記載されたテーブル名・フィールド名で
サンプルを作成して動作確認しています。
 
推測ですが、私の記述した SQL を
実際のテーブル名 や フィールド名 に置き換えようとして
エラーになっているのではないでしょうか。
 
だとすると、テーブルやフィールドの「 命名規則 」に問題があります。
 
 ・ 機種依存文字( 丸囲み数字やローマ数字 )
 ・ 全角記号
 ・ アンダースコア以外の半角記号全て
 ・ スペース
 
テーブル名やフィールド名に こういった文字が使われると
SQL文中で、オブジェクト名を [] でくくらなくてはいけなくなります。
 
  例:  ID-5 という名前の列だと [ID-5] と記述します
 
# 本来は、01元データ というような 数字から始まるオブジェクト名 や
# 全角英数字 の使用も好ましくありません。
# 2003 くらいまでの古いバージョンだと、これらも NG です
 
こういったオブジェクト名を使用していると
フォームやレポートの処理にも悪影響を及ぼしますから
カッコ付与の一時しのぎではなく、原因の排除となる 名前の修正 をお薦めします。

投稿日時: 19/08/08 18:46:05
投稿者: 忠犬

mayuさん、ご指摘ありがとうございます。
フィールド名を見直しても相変わらずfrom 句のエラーが出てしまい、前に進みそうになかったため、最初に教えていただいたクエリを別テーブルに書き出し、そのID と元データのID をつなげてみたところ思い通りに表示されました!
 
色々ご指南いただきどうもありがとうございました!
最後に教わったクエリは、どこに問題があったのか探していくつもりです。
 
またわからなくなったら質問させてください。
mayuさん、HALさん、回答いただきどうもありがとうございました!