Access SQL 重複クエリでいづれかの条件が一致した場合のクエリ

Question

いつもお世話になります。
Accessを使用してVBAやクエリを作成していますが、
いづれかが一致すればいい。というORな重複クエリを作れないでいます。

例えば、名前、電話番号、メールアドレスのいづれかが重複しているもの。
というものです。

以下のような構成があります。

■テーブル構成とデータ
伝票番号：名前：電話番号：email

111：クエリ太郎 ：111-222-3333：taro@examplecom　★
112：アクセス次郎 ：222-333-4444：jiro@examplecom
113：クエリ次郎 ：111-222-0000：taro@examplecom　★
114：クエリ株式会社：111-222-3333：info@compaycom　★
115：SQL次郎 ：999-999-9999：sql@examplecom
116：クエリ株式会社：000-000-0000：NULL 　★

■インデックス
名前、電話番号、emailです。

具体的には、★のついている以下の４つを抽出したいです。

111：クエリ太郎 ：111-222-3333：taro@examplecom
113：クエリ次郎 ：111-222-0000：taro@examplecom →メールアドレスが重複している
114：クエリ株式会社：111-222-3333：info@compaycom →電話番号が重複している
116：クエリ株式会社：000-000-0000：NULL →名前が重複している

つまり、名前、電話番号、メールアドレスのいづれかが一致すれば抽出するといった感じです。

また、データによっては、メールアドレスがないものもあります。★
この場合でも名前が一致しているので抽出したいです。ここが難しいです。

勉強中のサブクエリというものを使うのかもしれませんが、難航しています。
アドバイスいただければ幸いです。
よろしくお願いいたします。

Comments

[YT0014]

メールアドレスがないレコードが複数あり、それ以外に重複がない場合は、抽出対象でしょうか？

[sparc]

レスポンスありがとうございます。
メールアドレスがないレコードは複数あります。
その場合は、名前と電話番号が重複していなかったら、対象外としたいです。

名前が一致した場合は○
名前が一致しなくても電話番号が一致すれば○
名前と電話番号が一致しなくてもメールアドレスが一致すれば○
それ以外は×

結果としては、リピーターを抽出したいのです。
会社名はお客様によっては、あったりなかったり、電話番号も変わる可能性もあり、メールアドレスがない場合もあります。故にこのような複雑な条件になっています。

といった感じなのですが、お伝えできましたでしょうか。
引き続きご検討いただければ幸いです。

[YT0014]

各カラムのインデックスの状態を追記してください。

[sparc]

ありがとうございます。追記しました。
当初はインデックスなしでしたが、アドバイスいただいたとおり、名前、電話番号、emailを追記してみました。→改善されず

Answer 1

クエリーのみだと駄目だという事なので、一旦情報をテーブル化してみて下さい。
以下のテーブル作成クエリーでテーブル化します。

select 名前 into 名前_重複
from tbl
where 名前 is not null
group by 名前
having count(*) > 1

select 電話番号 into 電話番号_重複
from tbl
where 電話番号 is not null
group by 電話番号
having count(*) > 1

select email into email_重複
from tbl
where email is not null
group by email
having count(*) > 1

それぞれ実行したら、以下のSQLを実行します。

select * from tbl
where 名前 in (select 名前 from 名前_重複)
   or 電話番号 in (select 電話番号 from 電話番号_重複)
   or email in (select email from email_重複)

もし、元になっているテーブルが、ODBCリンクテーブルであるなら、そのテーブルをローカルのテーブルとしてコピーして実行してみる事ですね。

若しくはパススルークエリーであればクエリーのままでも結果が却ってくると思います。

select * from tbl
where 名前 in (
        select 名前 from tbl
        where 名前 is not null
        group by 名前
        having count(*) > 1
      )
  or 電話番号 in (
        select 電話番号 from tbl
        where 電話番号 is not null
        group by 電話番号
        having count(*) > 1
      )
  or email in (
        select email from tbl
        where email is not null
        group by email
        having count(*) > 1
      )

追記

レコードの移動するたびに、この機能（リアルタイムでリピーターかどうか）をチェックしたい

ということなら、比較対象元は限定されるはずですから、その条件でのクエリーなら問題ない気がします。

select * from tbl
where 伝票番号 in (
        select 伝票番号 from tbl
        where 伝票番号 <> [処理対象の伝票番号]
          and (
                 名前=[処理対象の名前]
              or 電話番号=[処理対象の電話番号]
              or email=[処理対象のemail]
              )
      )

※[]内は該当レコードの値
フラグだけなら以下でも良いと思います。

DCount("*","tbl","[上記の条件]")>0

Comments

[sparc]

sazi様
アドバイスありがとうございます。
テーブル化ですか！ちょっと時間をいただきますが、ぜひやってみます。
またご報告させていただきますので、引き続きお時間が許せばよろしくお願いいたします。

[sazi]

ODBCリンクテーブルなのであれば、パススルークエリーにするのが手っ取り早いですよ。

[sparc]

sazi様
一旦テーブル化ですが、かなり改善されました。
レコード数1.6万件あっても、無応答→5秒くらいです。

ただ、これってレコードの移動するたびに、この機能（リアルタイムでリピーターかどうか）をチェックしたいので、5秒でもかなり辛いんです。
そんなところであれこれやってみましたが、あまり変わらないですね。

やったこと→テーブルからクエリを作成して、カラムと対象データを絞る
その作成したクエリに対して、SQLを発行

ちなみに、テーブルはローカルです。
（ODBC、パススルーともにアドバイスを元にググり勉強させていただきました、ありがとうございます）

それでもひとまずこれがベストなのかなーと今は思っています。
とても勉強になりました、ありがとうございます。

[sazi]

それぞれのテーブルにインデックスを設定する事で改善されるかもしれません。
その為にはテーブル作成クエリーではなく、削除＋追加クエリーに置き換えが必要です。
若しくは、テーブル作成の後でインデックスを追加する。
但し、元テーブルはマルチカラムインデックスになるので、使われないかもしれません。
その場合には、条件をunionクエリーに置き換える方法が考えられます。

[sazi]

> レコードの移動するたびに、この機能（リアルタイムでリピーターかどうか）をチェックしたい
ということなら、比較対象元は限定されるはずですから、その条件でのクエリーなら問題ない気がします。

[sparc]

sazi様
あの。。。いただいたソース、完璧です。
私が２日かかって考えていたものです。
私の思っていることを数行で読み取ってくださり、ソースまでいただけて本当に感謝しています。
＃最初からそのように書けばよかったのだと反省しております。

いただいたソースも少し理解しました。
おかげさまで、これまで作っていた処理も効率できそうです。
とても勉強させていただきました。

ありがとうございます。

[sparc]

sazi様
本日はありがとうございます。
いただいたソース（追記でいただいた文）を元に構築したところ、
条件によって問題があり、申し訳有りませがご相談させていただけないでしょうか。

例えば、該当レコードにemailが入っていない場合うまく抽出できません。
emailがない他のレコードも抽出されてしまいます。

この場合、いただいたソースの中でif文などで振り分けたりすればいいのかと思っていますが、そのようなことは可能なのでしょうか？

つまり、andで囲われた部分をif文やcase文で振り分ける。
または、該当レコードの値がヌルの場合は実行しないなどの処理

この線で調べながらトライしていますが、今の所文法エラーから抜け出せておりません。
もし、間違った方向でしたらご指導いただければ幸いです。
よろしくお願いいたします。

[sparc]

すみません、どうもACCESSでは一般的なCASE文は使えなそうです。
https://itlogs.net/access-sql-switch/#toc2
文法エラーはこれかもしれません、引き続きトライしてみます。

[sparc]

sazi様
大変失礼しました。
こちら、SQLの問題ではなく、アクセス側（レコード）に入っている改行コードの違いで発生する問題ということが分かりました。
よって、申し訳ありませんが、今回のご相談はなかったことにしてください。
お騒がせしてすみませんでした。

[sazi]

accessのSQLにはcase文はありません。代わりにIIf()やNz()を使用します。

[sparc]

ありがとうございます。
うまく抽出出来ない原因は、元レコードは、outlookから取り込んだデータなのですが、例えばemailが空欄の場合、ヌルが入っていなかったので、抽出結果に矛盾が生じたようです。
空欄にヌルを入れるとうまく動くことが分かりました。
SQL文は全く問題ございません。
ありがとうございました、今後ともよろしくお願いいたします。

[sazi]

値無しかどうかの比較の際、「Nz(trim([項目]))=""」という式をよく使います。
この式で、空白と空文字とNULLを纏めて判断できます。

Answer 2

Accessの機能だけで作成する方法

重複クエリウィザードで、３つの重複クエリを作成する。
その際、出力対象は、該当全カラム(伝票番号,名前,電話番号,email)
例えば、"名前重複"、"電話重複"、"メール重複"という名称にする。

上記3つの重複クエリをunionクエリで結合。

SELECT * FROM 名前重複
UNION SELECT * FROM 電話重複
UNION SELECT * FROM メール重複
;

サブクエリを使ったSQLを作成する方法

重複しているレコードを検索するSQL

上記ページで紹介されている3つ目のSQLをベースに、修正。

SELECT * FROM テーブル 
WHERE
  名前 IN ( 
    SELECT 名前 FROM テーブル GROUP BY 名前
      HAVING COUNT(名前) > 1
  ) 
  OR
  電話番号 IN ( 
    SELECT 電話番号 FROM テーブル GROUP BY 電話番号
      HAVING COUNT(電話番号) > 1
  ) 
  OR
  email IN ( 
    SELECT email FROM テーブル 
      WHERE email IS NOT NULL GROUP BY email
      HAVING COUNT(email) > 1
  )
;

申し訳ありませんが、手元にAccess環境がないため、未検証です。

Comments

[sparc]

YT0014様、ありがとうございます。
hatena19様と同じなのですが、Accessの機能版とサブクエリ版ともに、クエリが無限ループのようになってしまい、終わりません。

Accessのクエリウイザードでは、JOINのクエリを実行するとダメになってしまいます。
個々のクエリは大丈夫です。

今色々チャレンジしていますが、hatena19様と同じでやっていることは悪くないと思うので、
困りました。
ちなみに全レコード数は16,000件ほどで内訳は、
電話番号重複クエリ：5000件
会社名重複クエリ：3500件
メール重複クエリ：4000件といった感じです。

[YT0014]

実行環境のPCのメモリとディスクの空き容量をご確認ください。
状況を見る限り、メモリスワップが起きまくっているように思えます。
該当ファイルを複製し、全レコード数が1000件程度にしぼった状態でのテストをお願いします。

[sparc]

もしかしたらこれかもしれないです。
データ1.6万件でも該当するかもしれません。
http://www.nabobiz.work/2020/06/25/heaven-05/

といっても40秒とかそういう範囲ではなく終了しないのですが。。。

＞どうも「or」条件をつけると遅いのです。
＞絞り込むため結果レコード数は少なくなるのですが、10 件該当したとしても 40 秒以上かかっていました。
＞ところが「or」がつかない条件だと、結果は 4 万件あっても 15 秒程度で終わるのです。
＞ODBC トレースをとった結果、Where 条件式に「or」が入ると ODBC ドライバはお手上げするらしく、
＞or 条件以外の式を評価した結果のレコードセットをいったん作成し、Fetch して再
＞度絞り込みを行っていました。

[sparc]

環境のアドバイスに気づかず、レスして失礼しました。
確かにそうですね、レコード数絞ってみてやってみます。

[sparc]

お世話になります。
仰せのとおり、レコード数を1000に絞ると少し遅いですが、問題ありませんでした。
やはりデータ数とOR条件の問題のようです。
後ほど、saziさんご提案の件を実施してみたいと思います。

[sparc]

YT0014様

おかげさまで解決いたしました。
結果としては、私の困っていることを正確に伝えていなかったことで、
少しズレてしまったところがあったかもしれません、申し訳ありません。

YT0014様には、unionクエリという結合方法を学ばせていただきました。

SQLはまだまだ勉強途中でございます。
また何か機会がありましたらお付き合いいただければ嬉しいです。
今後ともよろしくお願いいたします。

Answer 3

下記でどうでしょうか。

SELECT a.*
FROM tbl伝票 AS a
WHERE EXISTS (
  SELECT * FROM tbl伝票 AS b
  WHERE a.伝票番号<>b.伝票番号 And
       (a.名前=b.名前 OR a.電話番号=b.電話番号 OR a.email=b.email)
);

Comments

[sparc]

hatena19様
アドバイスありがとうございます。
ものすごいことをされているので、短い時間ではきちんと把握できていませんが、
こちらの環境に当てはめてみたところ、クエリを実行が永遠終わらず、アクセスがハングしてしまいました。

テーブルの名前と名前を会社名にしたくらいのつもりですが、
もしよければ、どこかお気づきの点などあれば教えていただければ幸いです。

SELECT a.*
FROM Tメイン AS a
WHERE EXISTS (
SELECT * FROM Tメイン AS b
WHERE a.伝票番号<>b.伝票番号 And
(a.会社名=b.会社名 OR a.電話番号=b.電話番号 OR a.email=b.email)
);

[sparc]

hatena19様

YT0014様にも教えていただいたSQLでも同様、ハングしてしまいました。
ですので、hatena19様のSQLに問題があるものだとは思えません。失礼しました。

レコード件数自体は、15000件ほどなので大きくはないと思うのですが。。
なぜこのようになっているのか、今調べています。

[hatena19]

件数を減らしたテーブルを作成してみて(100件ぐらい)、それを対象に実行したらどうなりますか。
うまくいくなら重すぎて時間がかかっているということですね。

伝票番号、会社名、電話番号、email フィールドにインデックスを設定したら改善するかも知れませんので、試してみてください。

[hatena19]

インデックスでも改善せずに、単独の重複クエリがそれほど時間がかからないのなら、
テーブルに「重複チェック」フィールドを追加して、単独の重複クエリを更新クエリにして、「重複チェック」フィールドをTrueに更新するようにして、その後[重複チェック]=True で抽出するという方法も検討されてはどうでしょうか。

[sparc]

もしかしたらこれかもしれないです。
データ1.6万件でも該当するかもしれません。
http://www.nabobiz.work/2020/06/25/heaven-05/

といっても40秒とかそういう範囲ではなく終了しないのですが。。。

＞どうも「or」条件をつけると遅いのです。
＞絞り込むため結果レコード数は少なくなるのですが、10 件該当したとしても 40 秒以上かかっていました。
＞ところが「or」がつかない条件だと、結果は 4 万件あっても 15 秒程度で終わるのです。
＞ODBC トレースをとった結果、Where 条件式に「or」が入ると ODBC ドライバはお手上げするらしく、
＞or 条件以外の式を評価した結果のレコードセットをいったん作成し、Fetch して再
＞度絞り込みを行っていました。

[sparc]

hatena19様
ごめんなさい。コメントへの回答はteratailからメールが届かないのですね。
回答いただいたのに気づかず、勝手な憶測を書いてしまいました。
おっしゃるとおり、データ数の問題かもしれません。
アドバイス頂いた内容を試してみます。
少しお時間いただきますが、よろしくお願いいたします。

[sparc]

お世話になります。
レコード件数を減らすと劇的に改善することが分かりました。このため、インデックスを付けましたが改善されない状態です。
アドバイスいただいた、重複チェックフィールドの件ですが、申し訳ございません。
現在の私のスキルだと組み込めないので、まずは、saziさんのテーブル化というものを試してみたいと思います。

[hatena19]

ODBCリンクテーブルなら、ローカルにインポートしてからそのテーブル対して実行すると改善しそうです。

[sparc]

すみません。これでもテーブルはローカルなのです。
saziさんの作戦（一旦テーブル化する）で、かなり改善（5秒ほど）されたのですが、これが限界かなとも思っています。

[sparc]

hatena19様
おかげさまで解決いたしました。
結果としては、私の困っていることを正確に伝えていなかったことで、
少しズレてしまったところがあったかもしれません、申し訳ありません。

hatena19様には、WHERE EXISTSの文法を学ばせていただきました。

また何か機会がありましたらお付き合いいただければ嬉しいです。
今後ともよろしくお願いいたします。