Access 不一致クエリで差分を求める方法 追加 削除 内容変更を区別する

概要

この記事でわかること

• 不一致は指定キーの有無を調べる
• 追加と削除候補は比較方向を逆にする
• 内容変更は一致したキーの値を比較
• 結果を確認してから更新処理を別途設計

この記事の主な内容

1. 不一致クエリで分かる差分と分からない差分
2. 比較キーとサンプルテーブルを準備する
3. 不一致クエリウィザードで未登録を抽出する
4. SQLで両方向の差分と内容変更を調べる
5. 差分が合わない原因と複合キーの扱い
6. 差分確認から安全な取込運用につなげる
7. まとめ
8. よくある質問

想定読了時間：約12分～16分

不一致クエリで分かる差分と分からない差分

不一致クエリで分かるのは、比較に使ったキーが一方にだけ存在するかどうかです。「同じ顧客IDで住所が変わった」といった内容変更は、顧客IDだけを比較する不一致クエリでは見つかりません。

たとえば、前回CSVと今回CSVを顧客IDで比較する場合、今回だけにあるIDは追加候補、前回だけにあるIDは削除候補です。両方にあるIDは存在が一致しているため、住所や金額が変わっていても通常の不一致結果からは除かれます。

「削除候補」は、実際に削除してよいという意味ではありません。今回CSVが全件ではなく一部の顧客だけなら、載っていない顧客も有効なままです。比較対象の期間、部門、出力条件がそろっているかを先に確認しましょう。

比較キーとサンプルテーブルを準備する

比較の精度はキーの選び方で決まります。顧客なら顧客ID、伝票なら伝票番号、伝票明細なら伝票番号と明細番号の組み合わせなど、何を1件として扱うかに合わせて選びます。

練習用の前回と今回を用意する

練習用ACCDBにT_前回とT_今回を作り、どちらも顧客ID（短いテキスト、主キー）、顧客名（短いテキスト）、金額（通貨型）を持たせます。下の値は架空の比較用データです。金額の空欄はNullを表します。

期待結果は、追加候補C04、削除候補C01、金額変更C02です。C03は両方の金額がNullなので、ここでは変更なしと定義します。まずこの3種類を別々に取り出せるか確認します。

データ型とキーのルールをそろえる

文字列の「00123」と数値の123は、業務上の意味を確認せず同じものとして扱ってはいけません。先頭ゼロが識別情報なら短いテキストとして取り込みます。比較の直前に数値化すると、元のIDを復元できなくなる場合があります。

また、外部CSVを別々のテーブルへ取り込んだ後に採番されたオートナンバーを比較キーにすると、同じ業務レコードを結び付けられない場合があります。比較にはCSV内で一貫して管理されている業務キーを使いましょう。

全角・半角、前後の空白、記号、照合の規則も確認します。正規化する場合は元の列を残し、別の列や前処理クエリで補正内容を確認できるようにします。Accessの通常の文字列比較を、バイト単位の完全一致検査と同一視しないことも大切です。

不一致クエリウィザードで未登録を抽出する

追加候補を探す場合は、今回のデータを最初に選び、比較先として前回のデータを選びます。最初に選ぶ側が結果に残したい側です。方向を逆にすると削除候補の抽出になります。[S1]

1. ［作成］→［クエリウィザード］→［不一致クエリウィザード］を選択します。
2. 不一致を抽出するテーブルとしてT_今回を選びます。
3. 比較先のテーブルとしてT_前回を選びます。
4. 両方の顧客IDを選び、対応するフィールドの組み合わせとして設定します。
5. 結果に表示するT_今回の顧客ID、顧客名、金額を選択します。
6. Q_追加候補という名前を付け、結果表示を選んで完了します。

結果がC04の1件だけか確認します。C02は金額が変わっていますが、顧客IDが両方にあるため表示されません。ここでC02も表示されるはずだと考えると、正しい結果を誤りと判断してしまいます。

デザインビューで生成内容を確認する

保存したQ_追加候補をデザインビューで開きます。T_今回の全レコードと、それに対応するT_前回のレコードを返す外部結合になっていることを確認します。比較先の顧客IDにはIs Nullの条件が設定されます。

右側に一致レコードが見つからなかったとき、その側の列がNullになる性質を使っています。Null判定の列は、実際の一致レコードでは必ず値が入る主キーなどを選びます。任意入力の住所を判定列にすると、「顧客は存在するが住所が未入力」の行を誤検出するおそれがあります。

SQLで両方向の差分と内容変更を調べる

SQLビューでは比較方向と条件を文字で確認できます。以下はAccess SQLの選択クエリで、実行して一覧を見るだけではデータを追加・更新・削除しません。各SQLは別々のクエリとして保存してください。

今回だけにある追加候補

SELECT C.[顧客ID], C.[顧客名], C.[金額]
FROM [T_今回] AS C
LEFT JOIN [T_前回] AS P
ON C.[顧客ID] = P.[顧客ID]
WHERE P.[顧客ID] Is Null;

LEFT JOINは左側のT_今回を残す結合です。Pは前回、Cは今回の別名として使っています。一致先がないC04だけを、WHEREのIs Nullで取り出します。結合条件とNull判定を一組として理解すると、応用しやすくなります。[S2]

前回だけにある削除候補

SELECT P.[顧客ID], P.[顧客名], P.[金額]
FROM [T_前回] AS P
LEFT JOIN [T_今回] AS C
ON P.[顧客ID] = C.[顧客ID]
WHERE C.[顧客ID] Is Null;

Q_削除候補として保存すると、C01の1件が期待結果です。最初のSQLからテーブルの左右とNull判定先を両方変えています。片方だけ変えると、意図した結果になりません。

同じキーの金額変更を調べる

内容変更は内部結合で両方に存在するIDを集めてから、値を比較します。本例は金額だけを比較するため、顧客名の変更は検出しません。比較したい列を業務要件に合わせて追加してください。[S3]

SELECT C.[顧客ID],
       P.[金額] AS [前回金額],
       C.[金額] AS [今回金額]
FROM [T_今回] AS C
INNER JOIN [T_前回] AS P
ON C.[顧客ID] = P.[顧客ID]
WHERE C.[金額] <> P.[金額]
   OR (C.[金額] Is Null AND P.[金額] Is Not Null)
   OR (C.[金額] Is Not Null AND P.[金額] Is Null);

Q_金額変更候補の結果はC02の20,000円→25,000円です。単に「<>」だけにすると、一方がNullの変更を拾えません。このSQLは「Nullと値ありは変更」「両方Nullは変更なし」という判断を明示しています。

Nz(金額,0)を使うと式を短くできますが、Nullと0円の違いが消えます。未入力とゼロを別の意味で管理する業務では、上のようにNullの組み合わせを分けた方が意図を伝えられます。

差分が合わない原因と複合キーの扱い

期待した差分が出ない場合は、SQLを書き換える前に、比較キー、比較方向、対象範囲、重複、型を確認します。差分件数だけで正誤を判断せず、追加・削除候補・内容変更の代表行を目視で照合してください。

複数列をキーにする場合

伝票番号だけでは複数明細を識別できません。伝票番号と明細番号の組み合わせを使う場合は、次のように両方を結合条件にします。T_今回明細とT_前回明細には、伝票番号（短いテキスト）と明細番号（長整数）があり、両列の組を一意かつNull不可とする前提です。

SELECT C.[伝票番号], C.[明細番号]
FROM [T_今回明細] AS C
LEFT JOIN [T_前回明細] AS P
ON (C.[伝票番号] = P.[伝票番号])
AND (C.[明細番号] = P.[明細番号])
WHERE P.[伝票番号] Is Null;

不一致ウィザードでは最初に1組のフィールドを対応させ、完成後にデザインビューで残りの結合を追加する方法があります。複数の結合を追加する場合、すべて同じ方向の外部結合にそろえます。[S1]

複合キーを単純に文字連結して比較すると、「1と23」と「12と3」が同じ文字列になるなど衝突することがあります。各列をANDで結合する方が、境界や型を保ったまま比較できます。

重複キーを先に検査する

CSVの検査用テーブルでは主キーをまだ設定していないこともあります。その場合、次のSQLで同じ顧客IDが2件以上ないか確認できます。前回・今回それぞれで行い、重複を解消するか、より細かいキーを定義してから比較します。

SELECT [顧客ID], Count(*) AS [件数]
FROM [T_今回]
GROUP BY [顧客ID]
HAVING Count(*) > 1;

両方に同じIDが複数あると、一致比較では組み合わせの数だけ行が増えます。重複した1行を「多分不要」と判断して削除せず、別取引なのか二重取込なのかを確認してください。Nullキーも通常のキー比較では一致とならないため、先に別扱いにします。

差分取込を毎回手作業で確認している場合は、比較ルールと期待結果を整理すると自動化の要件になります。
既存AccessとCSV連携の調査・改修についてお気軽にご相談できます。

差分確認から安全な取込運用につなげる

不一致クエリを作っただけでは、取込処理は完成しません。追加候補をどの列へ書き込むか、変更を上書きしてよいか、削除候補を物理削除するか停止扱いにするかを別に決める必要があります。

再実行できる工程に分ける

1. 受信した元CSVを保管し、取込日時や対象範囲を記録します。
2. 作業用テーブルへ取り込み、行数、キー、型、Null、重複を検査します。
3. 追加候補、削除候補、内容変更を別クエリで出力します。
4. 件数と代表行を確認し、業務の判断が必要な差分を除外します。
5. バックアップと処理記録を用意してから、承認された範囲の更新処理を実行します。

同じCSVを再び受け取った場合に二重追加しないよう、業務キーの一意性や取込履歴を設計します。実行途中で止まったときに、どこまで反映されたか分からない処理は避け、更新件数とエラーを記録できるようにします。

差分件数を説明できるか確認する

練習例では、前回3件−削除候補1件＋追加候補1件＝今回3件となります。金額変更1件は、存在する件数を増減させません。この照合はキーが一意で比較範囲が同じ場合の確認方法です。

なお、実データの数値や住所には業務上の例外があります。金額の丸め、空文字とNull、削除対象の定義などをあいまいにしたまま自動化すると、正しいSQLでも意図しない更新になります。先に比較ルールを文章にしておきましょう。

まとめ

不一致クエリはキーの存在差を調べる道具です。追加候補と削除候補は比較方向を逆にし、同じキーの内容変更は別に調べます。キーと型をそろえ、Nullと重複を確認し、差分一覧を検算してから更新処理へ進めてください。

よくある質問

AccessのCSV差分取込と照合処理を相談する

本文の確認手順を試しても原因を絞れない場合は、元データの定義、使っているクエリ、期待する結果、実際の症状を整理してご相談ください。Accessの教科書では既存Accessの調査・保守・改修を支援しています。

参考情報

[S1] Microsoft　2つのテーブルを比較し一致するものがないレコードを検索する
https://support.microsoft.com/ja-jp/access/compare-two-tables-and-find-records-without-matches

[S2] Microsoft　LEFT JOINおよびRIGHT JOIN操作
https://learn.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/left-join-right-join-operations-microsoft-access-sql

[S3] Microsoft　2つのテーブルを比較して一致するデータだけを検索する
https://support.microsoft.com/ja-jp/access/compare-two-tables-in-access-and-find-only-matching-data

[S4] Microsoft　データ型の不一致に関するメッセージが表示される
https://support.microsoft.com/ja-jp/access/i-get-a-message-about-data-type-mismatch

[S5] Microsoft　Count関数
https://learn.microsoft.com/ja-jp/office/vba/access/concepts/criteria-expressions/count-function-microsoft-access-sql

関連記事

Accessの主キーとは 設定と重複対策｜Accessの教科書

Accessに外部データを取り込む方法｜Accessの教科書

Accessのテーブルとは？作り方・フィールド・主キーを解説｜Accessの教科書