2つのテーブル間でデータが重複するかしないか判別する
マスタテーブルに対して実績テーブル内のデータが重複するもの、しないものに分けてテーブルを作成します。
実際に結果テーブルを作成する必要は無いかもしれませんが、わかりやすくするために結果をテーブル化しています。
【今回のテーブルとモジュールのセット】
■テーブルの構成
テーブルを4つ用意します。
フィールド名、データ型はいずれも下記と同じ内容となります。
【各テーブルのフィールド名とデータ型】
氏名 短いテキスト
住所 短いテキスト
年齢 数値型
【マスタテーブル】
【実績テーブル】
【重複する結果テーブル】
【重複しない結果テーブル】
■テーブル内データの流れ
マスタテーブルに対して実績テーブルのデータが重複していれば重複する結果テーブルに格納し、重複していなければ重複しない結果テーブルに格納する。
■標準モジュールのVBA（重複しているデータ）
【以下VBA記述コード】
Sub マスタにいる人()
'------------------------------------------
'①【EXISTS】2つのテーブル間で重複しているデータを抽出
'------------------------------------------
Dim str As String
str = "SELECT * "
str = str & "FROM 実績テーブル WHERE EXISTS ("
str = str & "SELECT * FROM マスタテーブル "
str = str & "WHERE マスタテーブル.氏名 = 実績テーブル.氏名 "
str = str & "and マスタテーブル.住所 = 実績テーブル.住所 "
str = str & "and マスタテーブル.年齢 = 実績テーブル.年齢)"
'------------------------------------------
'②ADOで上記の重複データのテーブルセットを作成
'------------------------------------------
Dim cn As New ADODB.Connection
Set cn = CurrentProject.Connection
Dim rs As New ADODB.Recordset
rs.Open str, cn, adOpenKeyset, adLockReadOnly
'------------------------------------------
'③1行ずつ結果テーブルに格納する
'------------------------------------------
Dim simei As String
Dim jusho As String
Dim nenrei As Integer
Dim strsql As String
'↓↓↓重複する結果テーブルを初期化しておく↓↓↓
DoCmd.RunSQL "DELETE FROM 重複する結果テーブル"
Do Until rs.EOF
simei = rs.Fields("氏名")
jusho = rs.Fields("住所")
nenrei = rs.Fields("年齢")
strsql = "INSERT INTO 重複する結果テーブル(氏名,住所,年齢) VALUES ("
strsql = strsql & "'" & simei & "'" & ","
strsql = strsql & "'" & jusho & "'" & ","
strsql = strsql & nenrei & ")"
DoCmd.RunSQL strsql
rs.MoveNext
Loop
rs.Close
cn.Close
End Sub
■重複しているデータVBAについて
①【EXISTS】2つのテーブル間で重複しているデータを抽出
ここではマスタテーブルに対して実績テーブルの各データ、つまり「氏名」「住所」「年齢」が全て一致するレコードを抽出しています。
記述はクエリのSQL構文となっていて、”WHERE EXISTS”と言うところが重複を判定するミソとなります。
②ADOで上記の重複データのテーブルセットを作成
重複するデータが見つかった場合、格納するレコードセット”rs”を準備しています。
「rs.Open str, cn, adOpenKeyset, adLockReadOnly」
この部分で”rs”に重複データ”str”の内容をオープンしている。
③1行ずつ結果テーブルに格納する
「DoCmd.RunSQL "DELETE FROM 重複する結果テーブル"」
まず初めに「重複する結果テーブル」を削除クエリで初期化しておきます。
「Do Until rs.EOF」～
レコードセット”rs”を順番にデータが無くなるまで繰り返し処理します。
データがあれば、追加クエリ「INSERT INTO～」で「重複する結果テーブル」にデータを追加、格納していきます。
■標準モジュールのVBA（重複していないデータ）
【以下VBA記述コード】
Sub マスタにいない人()
'------------------------------------------
'①【not exists】2つのテーブル間で重複しないデータを抽出
'------------------------------------------
Dim str As String
str = "SELECT * "
str = str & "FROM 実績テーブル WHERE NOT EXISTS ("
str = str & "SELECT * FROM マスタテーブル "
str = str & "WHERE マスタテーブル.氏名 = 実績テーブル.氏名 "
str = str & "and マスタテーブル.住所 = 実績テーブル.住所 "
str = str & "and マスタテーブル.年齢 = 実績テーブル.年齢)"
'------------------------------------------
'②ADOで上記の重複データのテーブルセットを作成
'------------------------------------------
Dim cn As New ADODB.Connection
Set cn = CurrentProject.Connection
Dim rs As New ADODB.Recordset
rs.Open str, cn, adOpenKeyset, adLockReadOnly
'------------------------------------------
'③1行ずつ結果テーブルに格納する
'------------------------------------------
Dim simei As String
Dim jusho As String
Dim nenrei As Integer
Dim strsql As String
'↓↓↓重複しない結果テーブルを初期化しておく↓↓↓
DoCmd.RunSQL "DELETE FROM 重複しない結果テーブル"
Do Until rs.EOF
simei = rs.Fields("氏名")
jusho = rs.Fields("住所")
nenrei = rs.Fields("年齢")
strsql = "INSERT INTO 重複しない結果テーブル(氏名,住所,年齢) VALUES ("
strsql = strsql & "'" & simei & "'" & ","
strsql = strsql & "'" & jusho & "'" & ","
strsql = strsql & nenrei & ")"
DoCmd.RunSQL strsql
rs.MoveNext
Loop
rs.Close
cn.Close
End Sub
■重複しないデータVBAについて
①【NOT EXISTS】2つのテーブル間で重複しないデータを抽出
ここでは重複する場合との違いは「EXISTS」→「NOT EXISTS」となる点です。
それ以外はほぼ同じ処理をしています。
マスタテーブルに対して実績テーブルの各データ、つまり「氏名」「住所」「年齢」が完全一致しないレコードを抽出しています。
記述はクエリのSQL構文となっていて、”WHERE NOT EXISTS”と言うところが重複しない場合のを判定するミソとなります。
②ADOで上記の重複しないデータのテーブルセットを作成
重複しないデータが見つかった場合、格納するレコードセット”rs”を準備しています。
「rs.Open str, cn, adOpenKeyset, adLockReadOnly」
この部分で”rs”に重複しないデータ”str”の内容をオープンしている。
③1行ずつ結果テーブルに格納する
「DoCmd.RunSQL "DELETE FROM 重複しない結果テーブル"」
まず初めに「重複しない結果テーブル」を削除クエリで初期化しておきます。
「Do Until rs.EOF」～
レコードセット”rs”を順番にデータが無くなるまで繰り返し処理します。
データがあれば、追加クエリ「INSERT INTO～」で「重複しない結果テーブル」にデータを追加、格納していきます。
最後に
今回はクエリ（SQL）の形でテーブルの操作を記述しました。
場合によってはADO（AddNew～Updateメソッド）でテーブルに追加しても良いと思います。
そのあたりは慣れた方法で記述ください。
あとはこのまま、重複しないデータをマスタテーブルに追加して行くなり、用途に応じてシステムを組む感じです。
そもそもマスタテーブルに重複データを入れたくないって時などに役立つかもしれないですね。
ではでは。
