Access多表连接报错‘JOIN expression not supported’求助

你遇到的这个错误在Access多表关联查询里挺常见的，结合你的SQL语句，我整理了几个可能的原因和对应的解决办法：

1. 括号不匹配（容易被忽略的细节） #

仔细看你的SQL语句，FROM子句里的嵌套括号开头是((((((((cl_details T1)，一共8个左括号，但语句结尾没有对应的8个右括号来闭合！虽然你说检查过括号，但这个小疏漏很可能是触发错误的原因之一——Access的SQL引擎对括号完整性要求很高，哪怕少一个都会导致JOIN表达式解析失败。

2. Access对JOIN嵌套深度的限制 #

Access的Jet/ACE引擎对嵌套JOIN的层级有一定限制，你这里嵌套了8层左右，已经接近它的处理上限。这种情况下可以尝试减少嵌套层级，把部分JOIN改成平级关联，或者用子查询提前处理部分关联逻辑。

3. ON子句中混合关联与筛选条件的问题 #

你在和Sy_code_list表的JOIN里，把关联条件（比如t4.ID_Code = t7.ID_Code）和筛选条件（比如t7.ID_Code = 2）混在了同一个ON子句里，Access对这种复杂的ON条件支持不佳。建议把筛选逻辑移到子查询中，先过滤出需要的数据再做关联：

比如原来的写法：

Left Outer Join Sy_code_list T7 on t4.ID_Code = t7.ID_Code and t4.Level_2 = t7.Level_2 and t7.ID_Code = 2 and (t7.Level_3 = 0 or t7.Level_3 = -1)

改成子查询的方式：

Left Outer Join (
    SELECT ID_Code, Level_2, Full_Desc 
    FROM Sy_code_list 
    WHERE ID_Code = 2 AND (Level_3 = 0 OR Level_3 = -1)
) T7 on t4.ID_Code = t7.ID_Code and t4.Level_2 = t7.Level_2

4. 跨数据库连接的稳定性问题 #

你直接引用了外部mdb文件的表（[C:\Users\cjack\Desktop\Test Stuff\ecm_hol.mdb].tb_pers），虽然语法正确，但Access有时候对这种跨库直接关联的处理会有异常。可以试试先通过Access的外部数据菜单把这个mdb里的表链接到当前数据库，然后直接使用链接表的名称来关联，这样能提升查询的稳定性。

修改后的SQL示例 #

结合上面的建议，调整后的SQL大概是这样的：

SELECT 
    T1.ASSR_FEE as AssessorFee, 
    T1.CAU_DT as Comment, 
    T1.Incident_Date as IncidentDate, 
    T1.Inv_amount as InvoiceAmount, 
    T1.Rep_Cost as RepCost, 
    T1.cl_id_ecm as ReferenceId, 
    t1.Est_cost_init as EstimatedCost, 
    t6.name as Location, 
    T2.CO_Name & ' ' & T2.Surname as DriverName, 
    T3.CO_Name & ' ' & T3.Surname as InspectorName, 
    T7.Full_Desc as Customer, 
    T8.Full_Desc as CollectionPoint, 
    T9.Full_Desc as MainDescription, 
    T10.Full_Desc as Subdescription 
FROM 
    (
        (
            (
                (
                    cl_details T1 
                    LEFT OUTER JOIN [C:\Users\cjack\Desktop\Test Stuff\ecm_hol.mdb].tb_pers T2 ON T1.pers_no = T2.ref_id
                ) 
                LEFT OUTER JOIN [C:\Users\cjack\Desktop\Test Stuff\ecm_hol.mdb].tb_pers T3 ON T1.insp_id = T3.ref_id
            ) 
            LEFT OUTER JOIN cl_coding T4 ON T1.cl_id = T4.cl_id
        ) 
        LEFT OUTER JOIN Dealers T6 ON T1.cldeta_dealer0 = T6.dealer0
    )
    LEFT OUTER JOIN (
        SELECT ID_Code, Level_2, Full_Desc 
        FROM Sy_code_list 
        WHERE ID_Code = 2 AND (Level_3 = 0 OR Level_3 = -1)
    ) T7 ON t4.ID_Code = t7.ID_Code AND t4.Level_2 = t7.Level_2
    LEFT OUTER JOIN (
        SELECT ID_Code, Level_2, Full_Desc 
        FROM Sy_code_list 
        WHERE ID_Code = 11 AND (Level_3 = 0 OR Level_3 = -1)
    ) T8 ON t4.ID_Code = t8.ID_Code AND t4.Level_2 = t8.Level_2
    LEFT OUTER JOIN (
        SELECT ID_Code, Level_2, Full_Desc 
        FROM Sy_code_list 
        WHERE ID_Code = 5 AND (Level_3 = 0 OR Level_3 = -1)
    ) T9 ON t4.ID_Code = t9.ID_Code AND t4.Level_2 = t9.Level_2
    LEFT OUTER JOIN (
        SELECT ID_Code, Level_2, Level_3, Full_Desc 
        FROM Sy_code_list 
        WHERE ID_Code = 5 AND (Level_4 = 0 OR Level_4 = -1)
    ) T10 ON t4.ID_Code = t10.ID_Code AND t4.Level_2 = t10.Level_2 AND t4.Level_3 = t10.Level_3

你可以先试试修复括号的问题，再逐步调整其他部分，应该能解决这个错误。

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