2つのテーブルの電話番号から兄弟関係を抽出

前提・実現したいこと

・児童マスタテーブルから兄弟情報の入ったテーブルを更新したい。
・兄弟関係は２つのテーブルの電話番号の一致とする。

発生している問題

・電話番号が一致していても所々データが落ちる。（1000件中10件程度）
主として「兄IDのデータを処理したときには弟IDのレコードが取得できており」、
　　　　　その後「弟IDのデータも処理はされたけど兄IDのデータは取得できていなかった」

該当のソースコード

VBA
1Public Sub 児童マスタ_兄弟_更新()
2    Dim db As DAO.Database
3    Dim rs_m As DAO.Recordset
4    Dim rs_w As DAO.Recordset
5    Dim i As Long
6    
7    '兄弟用テーブルを削除して、児童マスタテーブルから最新データをコピー
8    Set db = CurrentDb
9    db.Execute "DELETE T_児童マスタ_兄弟.* FROM T_児童マスタ_兄弟;", dbFailOnError
10    db.Execute "INSERT INTO T_児童マスタ_兄弟 SELECT T_児童マスタ.* FROM T_児童マスタ;", dbFailOnError
11    
12    Set rs_w = db.OpenRecordset("SELECT * FROM T_児童マスタ_兄弟 ORDER BY ID")
13
14    Do Until rs_w.EOF
15    
16    	'電話番号が一致するものを兄弟と判定する。
17        Set rs_m = db.OpenRecordset( _
18            "SELECT * FROM T_児童マスタ WHERE 電話番号='" & rs_w!電話番号 & _
19                "' AND ID <>" & rs_w!ID & " ORDER BY ID;")
20
21        rs_w.Edit
22        
23        '兄弟（最大3人まで）
24        For i = 1 To 3
25            If rs_m.EOF Then Exit For
26            rs_w("在学兄弟姉妹クラス" & i) = rs_m!学年 & rs_m!クラス
27            rs_w("在学兄弟姉妹名" & i) = rs_m!名
28            rs_m.MoveNext
29        Next
30        rs_w.Update
31        rs_m.Close
32        rs_w.MoveNext
33    Loop
34
35    Set rs_m = Nothing
36    rs_w.Close
37    Set rs_w = Nothing
38    Set db = Nothing
39    
40End Sub

試したこと

兄弟関係の落ちたデータに関わる、電話番号の確認。
・ハイフンの文字コードも一致しているか。
・半角スペースや改行コードが混入していないか。

For文の中にDebug.Printを入れて、一件ずつチェック。
・問題はやはり発生しており原因不明

補足情報

Access 2016, 2010

momon-ga

2018/09/19 11:45

試したことは何を確認したの？なんとなくですがデータの問題では？

wakame01

2018/09/19 11:52

「試したこと」を少し詳しく書かせていただきました。よろしくお願いいたします。

tonkun4os

2018/09/19 12:09

Group Byて使えませんでしたかねー、データを先に正規化した方が良いですよー、後、データ構造が知りたいです(画面コピーでOK)

wakame01

2018/09/19 12:31 編集

データ構造は以前質問させていただいたこちらでもよろしいでしょうか。個人情報が詰まっているので、必要な部分だけ上手く画面コピーができません。 https://teratail.com/questions/51877 Group Byを使った方法が思い付きませんでした。よい方法がありましたら宜しくお願いします。

momon-ga

2018/09/19 12:56

T_児童マスタには、在学兄弟姉妹クラス1～3は、ないですよね？

wakame01

2018/09/19 13:09

ありません。T_児童マスタ_兄弟の方だけに必要なフィールドです。基本となるT_児童マスタからは除いてあります。

tonkun4os

2018/09/19 13:16

count関数とGroup Byで兄弟の要る電話番号は抽出出来ますから(countの結果が2以上ならば兄弟が要る)。後はそれを元に処理できませんか？

wakame01

2018/09/19 13:18

ありがとうございます。明日書き換えてみます。

ExcelVBAer

2018/09/20 03:07

４人目の兄弟とかではないのでしょうか？(３人目までは登録されるけど、４人目から落ちるので)

ExcelVBAer

2018/09/20 03:11

rs_w のキー(電話番号)に不安があるなら、値調整(Trim等)してアップデートした方が確実かと。

wakame01

2018/09/20 08:06

4人目の兄弟は存在しないことが確定しているので、現在の仕様では3人までです。

wakame01

2018/09/20 08:07

電話番号の値調整も改めて行ってみます。ありがとうございます。

行動規範の内容に同意します

回答2件

ベストアンサー

兄のほうに弟の情報が登録されるということは、rs_m取得時に兄と弟のひも付けができているということですので、データ不整合的な問題ではないような気がしています。

弟のほうに兄のデータが登録されないということですが、兄と弟の結びつけは前述のとおり一度できているはずですので、そもそもrs_wで弟が処理対象に含まれていないということはないでしょうか？

例えばrs_mを参照しない値(固定値など)で更新してみてはどうでしょう？
それでも弟の値が更新されてこないようならrs_wループで処理対象に含まれていない可能性が大です。

rs_wを取得する際、テーブル構造やレコード件数によってすべてのデータが取得できていない、ということがあるかもしれませんので一度ご確認ください。
見当違いでしたら申し訳ありません。

（追記 2018/09/21 12:00）

rs_w取得データサイズオーバーが原因ではなさそうでしたので、別の観点からアプローチしてみたいと思います。

まず、以下の点について確認をお願いします。
・この処理を複数回行った場合、毎回同じデータで問題が発生しますか？（処理結果にバラつきはありませんか？）
・毎回同じデータで問題が発生しているのであれば、問題が発生するデータになにか共通の特徴は見つかりませんか？（直前のデータの影響を受けている可能性もあります）

電話番号として登録されている文字に目には見えないゴミ文字(制御文字など)が含まれている可能性はないでしょうか？

電話番号で連結する際、左辺はSQL内でテーブルカラムを参照していますが、右辺はrs_wから取り出した値を文字列として記述しています。
この文字列への変換の過程で右辺だけ制御文字がはずれ、制御文字を含むデータとは紐づかなくなっている可能性があるかもしれません。

以下は、数字以外の文字を除去した電話番号で兄弟を探すサンプルコードです。

Option Compare Database

'引数を数値に変換
Function fncToNumber(ByVal vVal As Variant) As Variant
    
    Const sNUMS As String = "0123456789"
    
    Dim rep As Variant  '戻り値
    Dim i As Integer
    
    If IsNull(vVal) Then fncToNumber = Null: Exit Function
    
    '半角変換
    rep = StrConv(vVal, vbNarrow)
    
    For i = 1 To LenB(vVal)
        '1文字ずつチェック
        If InStr(1, sNUMS, Mid$(vVal, i, 1)) = 0 Then
            '数字でない文字は除去する
            rep = Replace(rep, Mid$(vVal, i, 1), "")
        End If
    Next i
    
    fncToNumber = rep
    
End Function

Public Sub 児童マスタ_兄弟_更新()
    Dim db As DAO.Database
    Dim rs_m As DAO.Recordset
    Dim rs_w As DAO.Recordset
    Dim i As Long

    '兄弟用テーブルを削除して、児童マスタテーブルから最新データをコピー
    Set db = CurrentDb
    db.Execute "DELETE T_児童マスタ_兄弟.* FROM T_児童マスタ_兄弟;", dbFailOnError
    db.Execute "INSERT INTO T_児童マスタ_兄弟 SELECT T_児童マスタ.* FROM T_児童マスタ;", dbFailOnError

    Set rs_w = db.OpenRecordset("SELECT * FROM T_児童マスタ_兄弟 ORDER BY ID")

    Do Until rs_w.EOF

        '電話番号が一致するものを兄弟と判定する。
        Dim strSQL As String
        strSQL = "SELECT * FROM T_児童マスタ WHERE fncToNumber(電話番号)='" & fncToNumber(rs_w!電話番号) & "'" & _
                 " AND ID <>" & rs_w!ID & " ORDER BY ID;"
        
        Debug.Print strSQL
        Set rs_m = db.OpenRecordset(strSQL)

        rs_w.Edit

        Debug.Print " rs_w   :ID[" & rs_w("ID") & "] TEL[" & rs_w("電話番号") & "]"
        
        '兄弟（最大3人まで）
        For i = 1 To 3
            If rs_m.EOF Then Exit For
            Debug.Print " rs_m(" & i & "):ID[" & rs_m("ID") & "] TEL[" & rs_m("電話番号") & "]"
            
            rs_w("在学兄弟姉妹クラス" & i) = rs_m!学年 & rs_m!クラス
            rs_w("在学兄弟姉妹名" & i) = rs_m!名
            rs_m.MoveNext
        Next
        rs_w.Update
        rs_m.Close
        rs_w.MoveNext
    Loop

    Set rs_m = Nothing
    rs_w.Close
    Set rs_w = Nothing
    Set db = Nothing

End Sub

このコードを使用しても弟から兄は紐づかないでしょうか？

投稿2018/09/20 03:48

編集2018/09/21 04:52

jawa

総合スコア3013

wakame01

2018/09/20 08:47

ありがとうございます。全てのデータが取得できていない可能性が高いので、固定値で更新処理をしてチェックしていきたいと思います。約0.5~1％程度データが落ちているので、その対策も考えていきたいと思います。

jawa

2018/09/20 13:33

momon-gaさんへの回答＞弟→兄が一致しないという現象はやはり起こっていました。が少し気になっています。これは「兄IDのデータを処理したときには弟IDのレコードが取得できており」、その後「弟IDのデータも処理はされたけど兄IDのデータは取得できていなかった」ということでしょうか？私はrs_wのデータ取得サイズが大きすぎて全件取得できていないような状況を推測しており、そもそも弟IDのレコードに対しては処理が行われていないのではないかと疑っていました。もし取得データサイズの問題であれば、rs_wに取得する件数を絞るなどすることで改善されるかもしれません。例えば「T_児童マスタ_兄弟テーブル」には「T_児童マスタ」の全件がINSERTされていますが、そのうち更新が必要なのは兄弟が１人以上存在するレコードだけですので、同一電話番号のレコード件数も抽出条件に加えることで取得件数を減らす、といった具合です。いろいろなアドバイスを受け、いろいろとチェックされていて大変だと思いますが、一度情報を整理させてください。

wakame01

2018/09/20 21:19

＞これは「兄IDのデータを処理したときには弟IDのレコードが取得できており」、その後「弟IDのデータも処理はされたけど兄IDのデータは取得できていなかった」その通りの状況になっています。データ取得サイズについても、1000件程度ですので大丈夫かと思っていましたが、確実を期すために抽出条件を加えて絞ってみたいと思います。

jawa

2018/09/21 03:06

私の当初の予想ははずれていたようですので、別の観点からのアプローチを追記させていただきました。ご確認ください。

wakame01

2018/09/21 10:26 編集

お忙しい中ありがとうございます。確認すべき点ですが、次の通りでした。・毎回同じデータ（箇所）で問題が発生します。・共通の特徴は見つかりませんでした。あえて共通するとすれば、それぞれのレコードに「学年」というフィールドがあるのですが、いずれも「1」か「2」でした。制御文字について rs_wとrs_mのそれぞれについてSetした直後にDebug.printで電話番号を書き出してみました。ご指摘の通り何かの制御文字が入っているようでした。サンプルコードを使わせていただき、結果を報告させていただきたいと思います。今自宅ですので、報告は週明けになりそうです。遅くなり申し訳ありません。

wakame01

2018/09/21 10:23

今職場に行ってサンプルコードの通りに書き換えて実行してきました。理想的な実行結果となりました。重ねてお忙しい中ありがとうございました。

行動規範の内容に同意します