Access数据库3张数据表对比验证的技术实现需求

1. 前置准备：创建临时变更记录表 #

先建立临时表存储职位/头衔变更记录，结构如下：

CREATE TABLE Temp_UserChanges (
    UserID TEXT(50) PRIMARY KEY,
    OldTitle TEXT(100),
    OldPosition TEXT(100),
    NewTitle TEXT(100),
    NewPosition TEXT(100),
    ChangeDate DATETIME DEFAULT NOW()
);

2. 验证任务1：检查Table C的UserID是否存在于Table A #

用SQL查询找出Table C中未在Table A出现的UserID：

SELECT c.UserID, c.Title
FROM TableC c
LEFT JOIN TableA a ON c.UserID = a.UserID
WHERE a.UserID IS NULL;

3. 验证任务2：检测Title/Position变更并写入临时表 #

先清空临时表避免重复数据，再插入变更记录：

DELETE * FROM Temp_UserChanges;

INSERT INTO Temp_UserChanges (UserID, OldTitle, OldPosition, NewTitle, NewPosition)
SELECT 
    c.UserID,
    b.Title AS OldTitle,
    b.Position AS OldPosition,
    a.Title AS NewTitle,
    a.Position AS NewPosition
FROM TableC c
INNER JOIN TableA a ON c.UserID = a.UserID
INNER JOIN TableB b ON c.UserID = b.UserID
WHERE (a.Title <> b.Title OR a.Position <> b.Position);

4. VBA 一键执行脚本 #

创建Access模块，粘贴以下代码实现自动化验证：

Sub RunUserValidation()
    Dim db As DAO.Database
    Dim rsMissing As DAO.Recordset
    Dim strSQL As String
    
    Set db = CurrentDb()
    
    ' 确保临时表存在（不存在则创建，存在则重建）
    On Error Resume Next
    db.Execute "DROP TABLE Temp_UserChanges;"
    On Error GoTo 0
    strSQL = "CREATE TABLE Temp_UserChanges (" & _
             "UserID TEXT(50) PRIMARY KEY," & _
             "OldTitle TEXT(100)," & _
             "OldPosition TEXT(100)," & _
             "NewTitle TEXT(100)," & _
             "NewPosition TEXT(100)," & _
             "ChangeDate DATETIME DEFAULT NOW());"
    db.Execute strSQL
    
    ' 检查缺失的UserID
    strSQL = "SELECT c.UserID, c.Title FROM TableC c LEFT JOIN TableA a ON c.UserID = a.UserID WHERE a.UserID IS NULL;"
    Set rsMissing = db.OpenRecordset(strSQL)
    
    If Not rsMissing.EOF Then
        MsgBox "发现" & rsMissing.RecordCount & "个UserID在Table A中不存在，请核查Table C数据。", vbExclamation
    Else
        MsgBox "所有Table C的UserID均存在于Table A中。", vbInformation
    End If
    rsMissing.Close
    
    ' 检测变更并写入临时表
    db.Execute "DELETE * FROM Temp_UserChanges;"
    strSQL = "INSERT INTO Temp_UserChanges (UserID, OldTitle, OldPosition, NewTitle, NewPosition) " & _
             "SELECT c.UserID, b.Title AS OldTitle, b.Position AS OldPosition, a.Title AS NewTitle, a.Position AS NewPosition " & _
             "FROM TableC c INNER JOIN TableA a ON c.UserID = a.UserID " & _
             "INNER JOIN TableB b ON c.UserID = b.UserID " & _
             "WHERE (a.Title <> b.Title OR a.Position <> b.Position);"
    db.Execute strSQL
    
    ' 打开报表展示结果
    DoCmd.OpenReport "rpt_UserValidation", acViewPreview
    
    Set rsMissing = Nothing
    Set db = Nothing
End Sub

5. 报表设计要点 #

创建名为rpt_UserValidation的报表，包含两个核心区域：

• 缺失UserID板块：数据源绑定任务1的查询结果，展示丢失的UserID及对应Title
• 变更记录板块：数据源绑定Temp_UserChanges表，展示UserID、新旧头衔/职位、变更时间

可在报表页眉添加标题，页脚补充脚本执行时间等辅助信息。

内容的提问来源于stack exchange，提问作者designspeaks