Accessで2つのテーブルを比較 追加 削除 内容変更を確認する方法

概要

この記事でわかること

• 比較キーとサンプルデータを準備する
• 片側だけにある行を両方向で抽出する
• 同じキーの内容変更とNullを比較する
• 複合キーと件数不一致を切り分ける

この記事の主な内容

1. 比較したい差分と基準時点を決める
2. 比較キーとサンプルデータを準備する
3. 片側だけにある行を両方向で抽出する
4. 同じキーの内容変更とNullを比較する
5. 複合キーと件数不一致を切り分ける
6. 比較結果を検算して取込につなげる
7. よくある質問

想定読了時間：約15分～20分

比較したい差分と基準時点を決める

2つのテーブルの比較では、行の有無と内容変更を別々に調べます。同じ顧客IDが両方にあって住所だけが変わった場合、IDの不一致クエリには出ません。逆に、一方にしかないIDは、内容を比較する前に追加・削除候補として分類します。

削除候補をそのまま削除しない

今回側にない行は、削除されたとは限りません。出力期間が違う、支店の範囲が違う、取込が途中で終わった、抽出権限が違う、といった原因でも消えたように見えます。まず同じ範囲・同じ定義のデータを比較しているかを確認します。

前回と今回の取得時刻も残します。片方だけが更新され続けるテーブルだと、調査中に差分が変わるためです。運用では比較用の取込テーブルへ固定したデータを保存し、取得日時や元ファイル名を記録すると結果を追いかけやすくなります。

同じ行を対応付けるキーを決める

比較キーとは、2つの表で同じ対象を指す値です。各テーブルで主キーになっていても、別々に採番したオートナンバーなら同じ対象を表すとは限りません。見た目が同じ番号というだけで結合せず、採番元と業務上の意味を確認してください。

1行が注文単位か、注文明細単位かも重要です。注文明細なら注文IDだけでなく、明細番号まで必要な場合があります。行の粒度が違う2つの表を、そのまま1対1比較することはできません。

比較キーとサンプルデータを準備する

教材ではT_前回とT_今回を使います。顧客IDは数値型・長整数で、重複なし・Nullなしとします。住所は短いテキスト型でNullを許可します。「Null」は未入力状態を表す表記で、文字列のNullを保存する意味ではありません。

前回は101・102・103・105・106の5件、今回は101・102・104・105・106の5件です。共通キーは4件、内容変更は2件、完全一致は2件になります。まずこの小さな例で期待結果を確認してから実データへ進めてください。

先に重複とNullを調べる

次のクエリで前回側の重複キーを調べます。今回側も同じ方法で確認します。結果が出る場合は、単純な1対1比較の前提が成立していないため、重複理由を整理するか比較キーを見直します。勝手に先頭の1件だけを採用しないでください。

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

Nullのキーは、SELECT * FROM [T_前回] WHERE [顧客ID] Is Null;で別に確認します。キーがNullの行同士は、通常の等値結合では一致する行として対応付けられません。値が欠けている理由を確かめ、比較対象から除くなら件数も記録します。

片側だけにある行を両方向で抽出する

不一致クエリウィザードは、片方の表に対応するキーが存在しない行を探すために使います。前回だけと今回だけを両方知りたいときは、表を選ぶ順番を逆にして2本作成します。方向が分かる名前を付けて保存しましょう。

ウィザードで今回だけの顧客を調べる

1. ［作成］→［クエリウィザード］→［不一致クエリウィザード］を選びます。
2. 結果に出したい側としてT_今回を選びます。
3. 比較先としてT_前回を選びます。
4. 両側の顧客IDを対応付け、必要な表示項目を選びます。
5. Q_今回のみとして保存し、顧客ID104だけが出ることを確認します。

前回だけを探す場合は最初にT_前回、比較先にT_今回を選びます。Q_前回のみの結果は顧客ID103です。元データを変える処理ではなく、差分候補を一覧にする選択クエリとして扱ってください。

SQLで処理の方向を確認する

次は今回だけにある行を取得するSQLです。LEFT JOINは左側のT_今回を残して結合し、対応する前回側のキーがない行をIs Nullで抽出します。前回の顧客IDはNullなし、という前提がここでも重要です。

SELECT N.[顧客ID], N.[住所]
FROM [T_今回] AS N
LEFT JOIN [T_前回] AS O
ON N.[顧客ID] = O.[顧客ID]
WHERE O.[顧客ID] Is Null;

前回だけの場合は次のように表の方向を逆にします。LEFT JOINの左右と、Is Nullで調べる側を合わせて変えます。SQLに慣れていない場合は、まずウィザードで作成したクエリをSQLビューで確認すると対応が分かります。

SELECT O.[顧客ID], O.[住所]
FROM [T_前回] AS O
LEFT JOIN [T_今回] AS N
ON O.[顧客ID] = N.[顧客ID]
WHERE N.[顧客ID] Is Null;

同じキーの内容変更とNullを比較する

共通の顧客IDを持つ行は内部結合で対応付け、住所などの値を比較します。前回値と今回値を両方表示すると、何がどう変わったかを利用者が判断できます。どちらの表の項目か分かる別名を付けてください。

空欄がない場合の基本と限界

住所が必ず入っている場合はO.[住所] <> N.[住所]で違いを探せます。しかし、片方がNullなら比較結果がTrueにならず、単純な不等号だけでは変更を取りこぼします。Nullから入力済みへ変わるケースも判定条件へ加えます。

SELECT O.[顧客ID],
       O.[住所] AS [前回住所],
       N.[住所] AS [今回住所]
FROM [T_前回] AS O
INNER JOIN [T_今回] AS N
ON O.[顧客ID] = N.[顧客ID]
WHERE (O.[住所] <> N.[住所])
   OR (O.[住所] Is Null AND N.[住所] Is Not Null)
   OR (O.[住所] Is Not Null AND N.[住所] Is Null);

教材では102の大阪から京都への変更と、105のNullから札幌への変更が出ます。106は両方がNullのため、ここでは同じ状態として扱います。比較項目が複数ある場合は、各項目の差分条件を括弧でまとめてORで結びます。

Nullと空文字を同じとみなすか決める

Nullは値がない状態、空文字は長さ0の文字列です。上記SQLはNullと空文字の変更も検出します。一方、業務上同じ空欄としてよいなら、Nz([住所],””)でNullを空文字に置き換えて比較する方法があります。

ただし、Nzで置き換えればいつでも正しいわけではありません。数値のNullを0へ変えると「未入力」と「0円」を同じにしてしまいます。違いを残す必要がある項目では、先ほどのようにNullの状態を別条件で比較してください。

表記ゆれと厳密な文字一致

住所の前後スペースを無視するか、全角半角をそろえるかは比較ルールです。元データをその場で書き換えず、比較用クエリに整形後の値を出し、元の値も残すと確認できます。Trimだけではすべての空白文字を取り除けるとは限りません。

Accessの文字比較はデータベースの照合規則に影響されます。大文字小文字まで区別する厳密比較が必要なら、通常の=や<>だけでよいかを確認し、Nullを分けたうえでStrCompなどの比較方法を検討します。日付も時刻を含むか、金額も型や丸め単位が同じかをそろえます。

複合キーと件数不一致を切り分ける

複数の項目を合わせて1行を識別するものを複合キーと呼びます。注文明細を比較するなら、注文IDと明細番号の両方で対応付ける例があります。文字列へつなげて比較する方法は、区切りやNullの扱いで誤一致が生じるため、原則として各項目を個別に結合します。

2項目を使った結合条件の例

SELECT O.[注文ID], O.[明細番号],
       O.[数量] AS [前回数量],
       N.[数量] AS [今回数量]
FROM [T_前回明細] AS O
INNER JOIN [T_今回明細] AS N
ON (O.[注文ID] = N.[注文ID])
AND (O.[明細番号] = N.[明細番号]);

これは別の教材例で、T_前回明細とT_今回明細に注文ID・明細番号・数量がある場合のSQLです。先ほどの顧客テーブル用SQLにそのまま貼り付けるものではありません。結合条件に使う2項目の組み合わせが、それぞれの表で重複していないことを確認します。

同じキーが2件と3件あると6件になる

前回側で同じキーが2件、今回側で3件あると、そのキーで結合した結果は2×3＝6件になります。これは比較処理が勝手にデータを増やしたのではなく、対応する組み合わせが複数あるためです。DISTINCTで隠す前に、粒度とキーを見直してください。

テーブル比較と差分取込の設計で困ったときは

まずは本記事の手順をコピーで試し、期待した結果との差を確認してください。作成者が不在の場合や、複数の画面・帳票・取込処理に影響する場合は、Accessの教科書へご相談ください。現在の構成、困っている操作、希望する結果を整理すると、調査すべき範囲を伝えやすくなります。

比較結果を検算して取込につなげる

キーが両側で一意かつNullなしなら、前回件数＝共通キー件数＋前回のみ、今回件数＝共通キー件数＋今回のみという関係で確認できます。さらに共通キーを、比較項目が一致する行と内容変更の行に分けます。

教材の検算結果

内容変更は「差分のある項目数」ではなく「変更がある行数」で数えます。1行で住所と電話番号の両方が変わっても、変更行数は1件です。別々の差分一覧を足し合わせると同じキーを重複計上するため、集計方法を明示してください。

比較と更新を別の工程にする

比較結果を出した後は、担当者が変更理由と件数を確認し、反映対象を決めます。追加クエリ・更新クエリ・削除クエリは別に設計し、バックアップ、実行記録、切り戻し方法を用意してから実行します。本記事のSQLは抽出のみで、元テーブルを更新しません。

まとめ

テーブル比較は、基準時点と範囲をそろえ、比較キーの品質を確認してから行います。両方向の片側差分と、共通キーの内容変更を分け、Nullや重複も考慮して検算しましょう。差分候補を確認する工程と、本番へ反映する工程を分離することが安全な運用につながります。

よくある質問

テーブル比較と差分取込の設計で困ったときは

まずは本記事の手順をコピーで試し、期待した結果との差を確認してください。作成者が不在の場合や、複数の画面・帳票・取込処理に影響する場合は、Accessの教科書へご相談ください。現在の構成、困っている操作、希望する結果を整理すると、調査すべき範囲を伝えやすくなります。

参考情報

不一致クエリ
https://support.microsoft.com/en-us/access/compare-two-tables-and-find-records-without-matches

抽出条件とNull
https://support.microsoft.com/en-us/access/examples-of-query-criteria

Nz
https://learn.microsoft.com/en-us/office/vba/api/access.application.nz

SELECT
https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/select-statement-microsoft-access-sql

関連記事

Access 不一致クエリで差分を求める方法と失敗しない確認手順

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

Accessのクエリとは？種類・抽出条件・作り方を解説｜Accessの教科書

Accessデータベースの基礎｜データベース基礎を初心者向けに解説