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.14

chayamati

お早うございます。
あなたのやりたいことはテーブルだけで機能します
添付はテーブルを実行モードで開いたものです。
各フィールド名の右端の∇で機能します。
(1)昇順
(2)フィルタ機能
　複数のフィールドに設定するとANDでの検索になります
(3)フィルタの詳細
-----------------------------------------------------------------------------
この機能は必要なフィールドだけの単純な選択クエリでも機能します

お礼

各フィールド名の右端の∇で表示されるポップアップ画面で
各フィールド毎に表示したい値を選択できますが
今回の質問のように、フィールド間の条件
(フィールドAの値に対してフィールドBの値が1つでないという条件)
は、この方法では指定することができないと思います。

なお、別の回答者様から頂きました回答で、
今回の問題は解決することができました。
ありがとうございました。

回答No.12

HohoPapa

>NO1の添付画像の例では、
>1
>2
>と表示されるSQL式を教えていただけないでしょうか。

↑の結果を得られればいいのであれば

SELECT FieldA
FROM TABLE1
GROUP BY FieldA
ORDER BY FieldA

です。

お礼

教えていただきました上記のSQL式では、
問題でない場合(FieldBの値が1つだけのFieldAの場合)
でも問題として抽出(表示)されてしまいます。
例えば、NO1の添付画像の例で、
6番目のレコードのFieldBがbではなくaだった場合、
正しいレコードなので抽出されないのが正しいのに、
抽出されてしまいます(1が表示されてしまいます)。

なお、別の回答者様から頂きました回答で、
今回の問題は解決することができました。
ありがとうございました。

回答No.11

chayamati

肝心な事を漏らしていました。
テーブルのデザインモードで、連読したフィールドを主キーにするには
左端のフィールドセレクタを範囲指定で右クリックから鍵マークを選択すると
IDの鍵マークが外れ、範囲指定したフィールドに鍵マークが付きます。
IDは残して下さい、他のテーブルとリレーションを組むときに使います。

お礼

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

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

回答No.10

chayamati

お早うございます。
回答No.５の続き
添付はお客様テーブルのデザインモードです。
各項目の設定は右端のクリックからはじめます。
(1)(4)郵便番号、都道府県、市区群町村の3つのフィールドで
　住所入力支援設定をしています。
　[郵便番号]→[都道府県、市区群町村]
　[都道府県、市区群町村]→[郵便番号]
　の変換が交互に可能です
　郵便番号、電話番号に定型入力設定で入力を定型化します。
(2)お客様名を入力するとその読みが「ふりがな」に反映されます
　またインデックスで重複なしに設定して二重登録のミスを回避します
(3)(8)IME入力モード,IME変換モードは入力時にモード切替の手数を省きます。
(5)標題　リレーションシップの主テーブルのキー（ID)へのフイールド名を
　??ID としますとデータ型は数字型ですがルックアップで
　主テーブルの名前を表示します。
　フォーム上にフィールドを配置するとフィールド名のラベルが付いてきます。
　このラベルの標記を強制します。
(6)レコードが追加される時の初期値を指定します。
　データ型が
　　数字の時は0
　　日付の時はDate()　ExcelのToday()
(7)お客様名等空白のフィールドのままレコードを追加出来ないようにします
-------------------------------------------------------------------------
この標準の項目は、データ型毎に異なります。
　日付型の時：カレンダー入力
　　（1か月分のカレンダが表示され日付けクリック）
　　　カレンダーは前月、次月と変更可能
　他はご自分で必要に応じて調べて下さい
添付の右下のルックアップはリレーションシップに欠かせないものです。

お礼

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

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

回答No.9

chayamati

こんばんは
前回、いい忘れました。VBAは不要で、テーブルデザインだけで解決ます。
ここから回答です。
＞主テーブル、補足テーブル、テーブルが何を指しているのかが
　よく分からないので、
★リレーションシップと云いますが、
　エクセルに無い、アクセスの最大の利点です。
　納品書のテーブルをデザインしてみましょう。
　納品書には、納品書No.、納入日、納入場所の
　　他お客様情報(お客様名、ご担当者名、住所、…）
　　　自社情報（自社名、担当者名、住所、…）
　　　「このテーブル名を納品書」とする
　　のおもて書き部分と
　　商品名、数量、単価、金額等の複数の明細行
　　　「このテーブル名を納品書明細」とする
　ここで、納品書の一つのレコードと複数の納品書明細のレコードを
　関連付ける仕掛けが必要になります。
　ここがリレーションシップ出番です。
・納品書
　ID
　納品書No.
　納入日
　納入場所
　お客ID
・納品明細
　ID
　納品書ID
　商品ID
　数量
　単価
　金額
-----------------------------------
お客様情報、商品情報はこれらのテーブルにはありませんが
別に、お客様マスタ、商品マスタに登録しておき、お客様ID、商品IDを
手掛かりに参照します。
また自社情報は1レコードのみのプロフィールと呼ぶテーブルに登録します。
　詳しくは次のURLを参照ください。
　https://dekiru.net/article/14945/
＞何をすればよいのかがよく分かりませんでした。
　できましたら、No1の回答者様の、
　添付画像の6個のレコードからなるテーブル(TABLE1)の例で、
　具体的な手順を説明していただくことはできないでしょうか。
★主キーレベルでは重複は未止められません
★現状のテーブルを提示頂けましたら、それに合わせて提案出来るのですが
　取り敢えず前述の「納品書」、「納品明細」を作成して下さい。
　Accessは、Excelに比べてナビが良いので、やってみて下さい。
　作成→テーブルデザインと進みます
　各テーブルともIDをとばして、次の項目から入力します。
　IDは流れの中で自動で鍵マーク付きの主キーとして作成されます。
　??IDの項目はデータ型を数値型にします
★テーブルが出来たら次はリレーションシップです。
　データベースツール→リレーションシップと進み、
　画面を右クッリックからテーブルを追加表示
　閉じるから関連のIDから？？IDへドラッグの後、参照整合性で閉めます。
★テーブルを開くとIDお任せで、情報の入力が可能です

お礼

教えていただきましたように
「納品書」と「納品明細」のテーブルを作成して
リレーションシップでIDによる関連付けまで行いました。

こちらの実際のテーブルでの操作なのですが、
・「TABLE1」は「納品書」に相当するのでしょうか。
　　もしそうだとすると、
　「納品明細」に相当するのは何になるのでしょうか。
・ リレーションシップの関連付けで、今回したい
　　FIELDAの値に対してFIELDBの値が1つでないFIELDAを列挙する
　　には、どうすればよいのでしょうか。
とい点がよくわかりませんでした。

なお、別の回答者様から頂きました回答で、
今回の問題は解決することができました。
ありがとうございました。

回答No.8

piroin654

No6のコードを少し訂正します。
データ修正の部分が都度修正になっているので、
以下に変更してください。

Sub test()
　　Dim db As DAO.Database
　　Dim rs1 As DAO.Recordset
　　Dim rs2 As DAO.Recordset
　　Dim strSQL As String
　　Dim strCheckData As String
　　Dim strfieldData As String
　　Dim boolcheck As Boolean

　　strSQL = "SELECT TABLE1.フィールドA FROM TABLE1 GROUP BY TABLE1.フィールドA;"
　　Set db = CurrentDb
　　Set rs1 = db.OpenRecordset(strSQL)
　　Set rs2 = db.OpenRecordset("TABLE1", dbOpenDynaset)
　　boolcheck = False
　　rs1.MoveFirst
　　Do Until rs1.EOF
　　　　rs2.MoveFirst
　　　　Do Until rs2.EOF
　　　　　　If rs1!フィールドA = rs2!フィールドA Then
　　　　　　　　If rs2!チェック = True Then
　　　　　　　　　　strCheckData = rs2!フィールドB
　　　　　　　　　　strfieldData = rs1!フィールドA
　　　　　　　　　　boolcheck = True
　　　　　　　　End If
　　　　　　End If
　　　　rs2.MoveNext
　　　　Loop

　　　　If boolcheck = True Then
　　　　　　rs2.MoveFirst
　　　　　　Do Until rs2.EOF
　　　　　　　　If rs1!フィールドA = rs2!フィールドA Then
　　　　　　　　　　If rs2!チェック = False Then
　　　　　　　　　　　　rs2.Edit
　　　　　　　　　　　　rs2!フィールドB = strCheckData
　　　　　　　　　　　　rs2.Update
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　rs2.MoveNext
　　　　　　Loop
　　　　End If
　　　　boolcheck = False
　　rs1.MoveNext
　　Loop

　　rs1.Close: Set rs1 = Nothing
　　rs2.Close: Set rs2 = Nothing
　　db.Close: Set db = Nothing
End Sub

お礼

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

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

回答No.7

piroin654

No6です。
No6のコード中で、

　　Dim strCheckData As String
　　Dim strfieldData As String

としていますが、これはフィールドAとフィールドBが
テキスト型と想定してのことで、数値型などの場合は
変更してください。

strCheckData はフィールドB
strfieldData はフィールドA

です。

お礼

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

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

回答No.6

piroin654

補足の中で、

＞正解となるテーブル(TABLE2)はありません。

＞では、正解がないのに、どうように直すのかというと、
＞不具合箇所を見て状況判断して直します。

とのことですから、
補足された内容から正確なデータの入ったテーブルは
存在しないようですし、また補足の中で問題となる
レコードを求めるSQLの書き方を求められていますが
そのようなことは一切この状況では出来ません。

したがって、No2の（4）で述べたようにする以外に
修正の方法はありません。この方法は要するに
修正する手間を以下に省略するかということです。

そこで、修正の手間を省く方法として以下のように
します。

(1)
TABLE1のデータが以下のようになっている場合、

ID　　フィールドA　　フィールドB　　フィールドC
1　　　　10　　　　　　ＵＵＵ　　　　　123
2　　　　5　　　　　　　ＦＦＦ　　　　9845
3　　　　10　　　　　　ＤＤＤ　　　　　9999
4　　　　12　　　　　　ＱＱＱ　　　　　54
5　　　　10　　　　　　ＡＡＡ　　　　　649
6　　　　8　　　　　　　ＦＦＦ　　　　　95
7　　　　5　　　　　　　ＲＲＲ　　　　37965

このテーブルにYes/No型のフィールドを一つ追加します、
フィールドの名前をチェックとしておきます。
なお、IDのような連続番号がないのならばなくてもかまいません。

(2)
以下のクエリを作成します。フィールドAを昇順並び替え、
データの比較をしやすくし、その場でチェックと修正を
するためです。

SELECT TABLE1.ID, TABLE1.フィールドA, TABLE1.フィールドB, TABLE1.チェック
FROM TABLE1
ORDER BY TABLE1.フィールドA;

なお、IDのような連続番号がないのならばなくてもかまいません。
このクエリを開き、正しいレコード一つにチェック入れます。

ID　　フィールドA　　フィールドB　　チェック
5　　　　10　　　　　　ＡＡＡ
3　　　　10　　　　　　ＤＤＤ　　　　　Ｘ
1　　　　10　　　　　　ＤＤＤ
4　　　　12　　　　　　ＱＱＱ
7　　　　5　　　　　　ＲＲＲ　　　　　　Ｘ
2　　　　5　　　　　　ＦＦＦ
6　　　　8　　　　　　ＦＦＦ

この表ではフィールドAが10の時、正しいフィールドBの値はＤＤＤ
とすればフィールドBの値がＤＤＤのレコードのどれか一つにチェックを
いれます。フィールドAの値が一つしかない場合はその場で正否を判断し、
手入力で修正します。たとえばIDが6のレコードのような場合です。

(3)
上記の作業が終了したら以下のコードを標準モジュールに貼り付け
実行します。これでデータが正しく修正されます。

なお、DAOを使いますので、Accessのバージョンによって
必要に応じて参照設定でDAOのチェックを確認してください。

なお、コード中のテーブル名、フィールド名などは実際に合わせて
変更してください。

Sub test()
　　Dim db As DAO.Database
　　Dim rs1 As DAO.Recordset
　　Dim rs2 As DAO.Recordset
　　Dim strSQL As String
　　Dim strCheckData As String
　　Dim strfieldData As String
　　Dim bk As Variant

　　strSQL = "SELECT TABLE1.フィールドA FROM TABLE1 GROUP BY TABLE1.フィールドA;"
　　Set db = CurrentDb
　　Set rs1 = db.OpenRecordset(strSQL)
　　Set rs2 = db.OpenRecordset("TABLE1", dbOpenDynaset)
　　rs1.MoveFirst
　　Do Until rs1.EOF
　　　　rs2.MoveFirst
　　　　Do Until rs2.EOF
　　　　　　If rs1!フィールドA = rs2!フィールドA Then
　　　　　　　　If rs2!チェック = True Then
　　　　　　　　　　strCheckData = rs2!フィールドB
　　　　　　　　　　strfieldData = rs1!フィールドA
　　　　　　　　　　rs2.MoveFirst
　　　　　　　　　　Do Until rs2.EOF
　　　　　　　　　　If rs1!フィールドA = rs2!フィールドA Then
　　　　　　　　　　　　If rs2!チェック = False Then
　　　　　　　　　　　　　　rs2.Edit
　　　　　　　　　　　　　　rs2!フィールドB = strCheckData
　　　　　　　　　　　　　　rs2.Update
　　　　　　　　　　　　End If
　　　　　　　　　　　　bk = rs2.Bookmark
　　　　　　　　　　End If
　　　　　　　　　　rs2.MoveNext
　　　　　　　　　　Loop
　　　　　　　　　　rs2.Bookmark = bk
　　　　　　　　End If
　　　　　　End If
　　　　rs2.MoveNext
　　　　Loop
　　rs1.MoveNext
　　Loop

　　rs1.Close: Set rs1 = Nothing
　　rs2.Close: Set rs2 = Nothing
　　db.Close: Set db = Nothing
End Sub

以上です。

お礼

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

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

回答No.5

chayamati

こんにちわ
質問内容を読み違えているかもしれません。
例えば果物でリンゴと言っても品種、サイズ、品質と区別しなければ品名を特定できません。
複数のフィールドを主キーにする事で重複登録を回避します。
図は衣類の体系です
M商品マスタテーブルを補足テーブルで対応します。
商品名は重複登録を避けるためインデックスで重複なしと定義します
品種ID、サイズID、色柄ID、品質ID一括で主キーとしてへリレーションします。
(1)4つの連読するテーブルを1括して主キーとします。
　※自動で作られるID(オートナンバー型は）売上てーブル等とのリレーションの為残します。
(2)ルックアップで補足テーブルをコンポに設定します。
　品種IDの設定画面で
　項目名が「品種ID」と表示されますが、
　標準タグの標題で「品種名」します。
(3)値集合ソースはM品種マスタ
(4)M品種マスタの最初のフィールド
(5)M品種マスタのIDと品種
(6)IDの表示幅０(非表示)品種はお任せ
(7)１度のプルダウン行数
(8)リスト幅お任せ
-----------------------------------------------------------------------------------------
もし私の考えが正しく、主テーブルと補足テーブルが作成できればこれに
テールをインポートすることで解決します
重複ではじき出されたレコードのエラーテーブルも作成されます。

お礼

>主テーブルと補足テーブルが作成できればこれに
>テールをインポートすることで解決します

の部分の、主テーブル、補足テーブル、テール(おそらくテーブル)
の3つのテーブルが何を指しているのかがよく分からないので、
何をすればよいのかがよく分かりませんでした。

できましたら、No1の回答者様の、
添付画像の6個のレコードからなるテーブル(TABLE1)の例で、
具体的な手順を説明していただくことはできないでしょうか。

よろしくお願いします。

回答No.4

HohoPapa

SELECT FieldA ,FieldB , count(MyNum) as MyCount
FROM TABLE1
GROUP BY FieldA ,FieldB
ORDER BY FieldA

と

SELECT FieldA ,FieldB , count(MyNum) as MyCount
FROM TABLE1
GROUP BY FieldA ,FieldB
ORDER BY FieldB

それぞれの結果セットを見比べ(手作業で？)加工することで
>正解となるテーブル(TABLE2)があるのであれば
これを作成できませんでしょうか。

お礼

NO2の回答者様のお礼コメントと重複しますが、

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

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

NO1の添付画像の例では、
1
2
と表示されるSQL式を教えていただけないでしょうか。

よろしくお願いします。