Access数据库按RecordID取最新培训记录JOIN语法错误排查

原代码的错误可分为两类：

• 语法层面：多层JOIN的括号嵌套不符合Access语法规范，括号匹配错误直接触发语法报错
• 逻辑层面：用于获取最新记录的子查询未按「员工ID+培训ID」分组，无法实现按员工去重取最新记录的需求，同时存在冗余子查询、重复GROUP BY的无效逻辑

SELECT 
    c.RecordID, 
    c.Employee, 
    e.EmployeeName,
    c.DecShotDate AS DSD, 
    c.DecShotLocation AS DSL, 
    c.DecReason AS DR, 
    c.DecExplanation AS DE,
    c.Training, 
    c.CompletedDate, 
    IIf([DecShotLocation]=0,"Hospital", 
        IIf([DecShotLocation]=1,"MCS", 
            IIf([DecShotLocation]=2,"Other Employer",
                IIf([DecShotLocation]=3,"Pharmacy",
                    IIf([DecShotLocation]=4,"Primary Care",
                        IIf([DecShotLocation]=5,"Urgent Care"," ")
                    )
                )
            )
        )
    ) AS StrDecLoc
FROM 
(
    (
        -- 子查询：获取每个员工对应每类培训的最新记录ID
        SELECT Employee, Training, MAX(RecordID) AS MaxRecordID
        FROM CompletedTrainings
        GROUP BY Employee, Training
    ) AS max_record
    INNER JOIN CompletedTrainings AS c 
    ON max_record.MaxRecordID = c.RecordID
)
INNER JOIN EmployeeInformation AS e 
ON c.Employee = e.ID
WHERE 
    c.Training = 69 
    AND (c.CompletedDate + 365) >= IIf(Forms![frm_Decflu]!FrameAllOrCurrent=1, c.CompletedDate + 365, Date());

• 如果实际使用的是示例中的tbl_E（员工表）、tbl_C（培训记录表），替换SQL中对应的表名、字段名即可
• 子查询通过分组取最大RecordID的方式，从根源上避免了同一员工同一培训的重复记录问题，不需要额外去重逻辑
• 调整后的JOIN结构完全符合Access语法要求，可直接运行

内容的提问来源于stack exchange，提问作者Christopher J. Joubert