ACCESS 以下のようなレコードを抽出するSQL

質問

ACCESSのデータベースで
あるフィールド(フィールドAとします)の値に対して、
別のフィールド(フィールドBとします)の値が1つに決まる
データベースがあるとします。
例えば、フィールドAの値が1のレコードは、
どのレコードもフィールドBの値はaである。など

このようなデータベースでこのような関係になっていないレコード
例えば、フィールドAの値が1のレコードの中に
フィールドBの値がaであるレコードと
フィールドBの値がbであるレコードがある。など
を抽出するSQLはどのように記述すればよいでしょうか。

テーブル名はTABEL1とします。

次の2つのケースでお願いします。

(1)フィールドAの値が異なればフィールドBの値が異なる場合
例えば、フィールドAの値が1で、フィールドBの値がaであるレコードが
ある時、フィールドAの値が1でないレコードの中には、
　 フィールドBの値がaであるレコードが存在しない場合

(2)フィールドAの値が異なるがフィールドBの値が同じこともある場合
例えば、フィールドAの値が1で、フィールドBの値がaであるレコードが
あっても、フィールドAの値が1でないレコードの中にも、
　 フィールドBの値がaであるレコードが存在する場合

【回答例】(ただし、1,a,bなどの具体的な値は使わないこと)

・フィールドAの値が1のレコードを表示する例
SELECT * FROM TABLE1 WHERE フィールドA=1;
・フィールドAの値が1でフィールドBの値がaのレコードを表示する例
SELECT * FROM TABLE1 WHERE フィールドA=1 AND フィールドB='a';
・フィールドAの値が1でフィールドBの値がaのレコードと
　フィールドAの値が1でフィールドBの値がbのレコードを表示する例
SELECT * FROM TABLE1 WHERE (フィールドA=1 AND フィールドB='a') OR (フィールドA=1 AND フィールドB='b');

この目的は、データベースに問題があり、
問題となっているレコードを見つけて直したいのです。

よろしくお願いします。

回答No.13

ID_20150222 · ベストアンサー

以下のSQL式でできないでしょうか。

SELECT FIELDA FROM (SELECT FIELDA,Count(FIELDA) AS COUNT_A FROM (SELECT FIELDA,FIELDB FROM TABLE1 GROUP BY FIELDA,FIELDB) GROUP BY FIELDA) WHERE NOT COUNT_A=1

お礼

教えていただきましたSQL式でできました。
これで解決です。
ありがとうございました。

回答No.3

piroin654

No2です。
＞（1）の場合のレコード数が違う場合は
というのは正確にはフィールドとフィールドBの
データの正しい組み合わせが一つずつあるテーブルで
TABLE1に複数フィールドAの値が同じレコードが
あってもTABLE元データには正しい組み合わせが
一つずつある、という意味です。

No2の（1）も（2）もデータを修正するときは
更新クエリで修正します。

お礼

別の回答者様から頂きました回答で、
今回の問題は解決することができました。

教えていただきました方法は今後の参考にさせていただきます。
ありがとうございました。

回答No.2

piroin654

最終目標が、

＞この目的は、データベースに問題があり、
＞問題となっているレコードを見つけて直したいのです。

とのことなので、質問の内容にはいくつか不明の点がありますが、
とりあえず以下のような場合分けで。

（1）
正確なデータが入っているテーブルをTABLE元データ
として、TABLE1とTABLE元データが同じ構造、同じ
レコード数ならば単純に差分クエリでTABLE1の
間違ったデータが表示されます。この場合フィールド数が
2以上あっても対象となるフィールドの差分をとります。

（2）
（1）の場合のレコード数が違う場合は、二つのテーブルを
比較し違うレコードを抽出する定義関数を作成し、それを
選択クエリに組み込んで違っているレコードを抽出します。

（3）
もし正確なデータが入っているテーブルが存在しない場合は
残念ながら魔法のテクニックがない限り手も足もでません。
そこで、正しいか正しくないかをその場で判断できるのであれば、
もしTABLE1のフィールド数が2以上あるならば比較するフィールド、
質問の場合はフィールドAとフィールドBを対象として

SELECT TABLE1.フィールドA, TABLE1.フィールドB
FROM TABLE1
ORDER BY TABLE1.フィールドA;

のように並び替えをしてデータの違いを確認し、その場で
データを修正します。

（4）
（3）の場合でデータ数が多い場合、TABLE1に数値型でも
yse/no型でもテキスト型でもいいのですがフィールドを
一つ追加し、正しいデータの一番最初のレコードにその
フィールドにチェックを入れます。ただし、レコードが
一つしかない場合はそれが正しくない場合はその場で
手入力で修正しなければなりません。
チェックを入れたレコード以外の同じフィールドAの
フィールドBを正しいデータにチェックの入っている
レコードのフィールドBの値に置き換えをする定義関数
なりSubプロシージャを作成し修正します。
以下は、クエリで並び替えをし、フィールドAが「10」
の場合フィールドBが「AAA」が正しい場合の時にチェックを
最初のデータに入れた状態です。
この場合、チェックはクエリ上で入力できます。

フィールドA　　　フィールドB　　　チェック
10　　　　　　　　ＡＡＡ　　　　　　〇
10　　　　　　　　ＰＰＰ
10　　　　　　　　ＡＡＡ
12　　　　　　　　ＱＱＱ
5　　　　　　　　ＡＡＡ

このようなところですかね。

お礼

返答が遅くなってしまって申し訳ありません。

この間にNo2,No3,No6,No7,No8の回答をいただきましたが、

データ数が非常に多いので、人が目で誤りを見つけて、
その都度、手で入力して直せるレベルでない状況です。
そこで、まず、第一ステップとして、
正しくないレコードを抽出して、
一旦、この抽出が完了した後に、
第一ステップの結果全体を見てから、
第二ステップとして、この対処方法を検討したいところです。

第一ステップとして、
正しくないレコードを抽出したいのですが、
正しくないレコードのフィールドAの値を表示する
SQL式を教えていただくことはできないでしょうか。

よろしくお願いします。

回答No.1

HohoPapa

>問題となっているレコードを見つけて直したいのです
例えば、添付画像のようなレコード群のとき
どのレコードたちが問題のレコードと判断すればいいんでしょうか？

>あるフィールド(フィールドAとします)の値に対して、
>別のフィールド(フィールドBとします)の値が1つに決まる
>データベースがあるとします。

FieldAとFieldBの本来あるべき対応表（テーブル）が別途あるんでしょうか？
それとも
FieldAとFieldBの組み合わせごとにレコードを数え
組み合わせごとにレコード数が最大の組み合わせが
問題となっていないレコードと判断するんでしょうか？
１位が複数の時にどうするのかも疑問です。

お礼

質問の表現が分かりにくくて申し訳ありませんでした。

質問の中の
「このようなデータベースでこのような関係になっていないレコードを抽出するSQLはどのように記述すればよいでしょうか。」
の部分の表現は、
「このようなデータベースでこのような関係になっていないレコードのフィールドAの値と同じ値を持つレコードを表示するSQLはどのように記述すればよいでしょうか。」
または、
「このようなデータベースでこのような関係になっていないレコードのフィールドAの値を表示するSQLはどのように記述すればよいでしょうか。」
という意味です。

これを踏まえて、以下の不明点についてお答えします。

(1)
>例えば、添付画像のようなレコード群のとき
>どのレコードたちが問題のレコードと判断すればいいんでしょうか？

問題のレコードは、
フィールドBの値がxのレコード(上から1番目と4番目のレコード)か、
フィールドBの値がyのレコード(上から2番目と3番目のレコード)の
どちらかですが、どちらなのかは分かりません。しかし、
フィールドAの値が2であるレコードであることには間違いないので、
フィールドAの値が2であるレコードをすべて表示します。
1 2 x あ
2 2 y　い
3 2 y　う
4 2 x　え
または、
このフィールドAの値だけを表示します。
2
複数あるのであれば、
2
90
777
:

(2)
>FieldAとFieldBの本来あるべき対応表（テーブル）が別途あるんでしょうか？

もし正解となるテーブル(TABLE2)があるのであれば、
SELECT TABLE1.FieldA,TABLE1,FieldB,TABLE2.FieldB FROM (SELECT * FROM TABLE1 Left Join TABLE2 ON TABLE1.FieldA=TABLE2.FieldA WHERE NOT TABLE1.FieldB=TABLE2.FieldB)
で不具合箇所を見つけられますが、
正解となるテーブル(TABLE2)はありません。
TABLE1の不具合が直れば、TABLE1からTABLE2は生成できます。
(その時にはその必要もありませんが)
では、正解がないのに、どうように直すのかというと、
不具合箇所を見て状況判断して直します。
例えば、上記の例では、他にも多くのレコードがあり、
FieldBの値がxのレコードが98個で、yのレコードが2個だけであり、
FieldAの値が2以外のレコードにおいて、
FieldBの値がyのレコードはあるが
FieldBの値がxのレコードがなければ、
FieldAが2のレコードのFieldBをすべてxにする。など

(3)
>FieldAとFieldBの組み合わせごとにレコードを数え
>組み合わせごとにレコード数が最大の組み合わせが
>問題となっていないレコードと判断するんでしょうか？
>１位が複数の時にどうするのかも疑問です。

問題のレコードを特定できなくても、
問題のレコードを持つFieldAの値が特定できればいいです。
(1)の添付画像の例では、
FieldBの値がxとyのどちらが正しいのかが特定できなくても、
FieldAの値が2であることが分かればいいです。
その後の判断は、私の方で、状況を見ながら判断します。
(具体的な状況を見てみないと分からないので)

よろしくお願いします。

補足

追記

すいません。
フィールドAの値が1のレコードの方が抜けていましたので訂正します。

(1)
フィールドAの値が1のレコードも問題なので、
フィールドAの値が2の場合と合わせると、
SQL式の結果は、
1　2　x　あ
2　2　y　い
3　2　y　う
4　2　x　え
5　1　a　お
6　1　b　か
または、
1
2
が正しいです。

(3)
FieldAの値が2であることが分かればいいです。
ではなく、
FieldAの値が1と2であることが分かればいいです。
が正しいです。

申し訳ありませんでした。