Access SQL更新最后一条记录：补全字段并修改状态

步骤1：定位目标记录 #

先执行查询获取指定员工的最新两条记录，确认待更新的最后一条记录和用于复制字段的倒数第二条记录的唯一标识（假设表有主键ID，无主键可结合Empcardno和Date定位）：

SELECT TOP 2 ID, Empcardno, Date, Status, Purpose, Location, Remarks
FROM TextfileData
WHERE Empcardno = [forms]![Main Check In Out Master]![Empcardno]
ORDER BY Date DESC;

步骤2：补全缺失字段值 #

通过更新查询，将倒数第二条记录的Status、Purpose、Location、Remarks复制到最后一条记录：

UPDATE TextfileData AS t1
INNER JOIN (
    SELECT ID, Status, Purpose, Location, Remarks
    FROM (
        SELECT TOP 2 ID, Status, Purpose, Location, Remarks, Date
        FROM TextfileData
        WHERE Empcardno = [forms]![Main Check In Out Master]![Empcardno]
        ORDER BY Date DESC
    ) AS sub
    ORDER BY Date ASC
    LIMIT 1
) AS t2 ON t1.Empcardno = t2.Empcardno
SET 
    t1.Status = t2.Status,
    t1.Purpose = t2.Purpose,
    t1.Location = t2.Location,
    t1.Remarks = t2.Remarks
WHERE t1.ID = (
    SELECT TOP 1 ID
    FROM TextfileData
    WHERE Empcardno = [forms]![Main Check In Out Master]![Empcardno]
    ORDER BY Date DESC
);

若Access版本支持OFFSET，可简化子查询：

UPDATE TextfileData AS t1
INNER JOIN (
    SELECT TOP 1 ID, Status, Purpose, Location, Remarks
    FROM TextfileData
    WHERE Empcardno = [forms]![Main Check In Out Master]![Empcardno]
    ORDER BY Date DESC
    OFFSET 1 ROWS
) AS t2 ON t1.Empcardno = t2.Empcardno
SET 
    t1.Status = t2.Status,
    t1.Purpose = t2.Purpose,
    t1.Location = t2.Location,
    t1.Remarks = t2.Remarks
WHERE t1.ID = (
    SELECT TOP 1 ID
    FROM TextfileData
    WHERE Empcardno = [forms]![Main Check In Out Master]![Empcardno]
    ORDER BY Date DESC
);

步骤3：修正Status状态 #

仅当最后一条记录的Status为"Gate Out"时，将其修改为"Gate In"：

UPDATE TextfileData
SET Status = "Gate In"
WHERE 
    ID = (
        SELECT TOP 1 ID
        FROM TextfileData
        WHERE Empcardno = [forms]![Main Check In Out Master]![Empcardno]
        ORDER BY Date DESC
    )
    AND Status = "Gate Out";

注意事项 #

• 执行更新前务必备份TextfileData表，防止数据错误。
• 确保表有唯一主键（如ID），避免记录定位偏差。
• 可通过VBA代码将两个更新操作封装为事务，保证操作的原子性。

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