如何在Microsoft Access中实现基于条件的查询连接？

问题说明 #

能否在Microsoft Access中根据特定条件修改两个查询的连接方式？我曾在其他环境中使用Case语句成功实现该功能，但不确定Access是否支持。以下是我过往使用的SQL代码，请问能否在Access中复现该查询？

原SQL代码：

select 
A.PROC,
A.MOD1, 
A.MOD2,
A.POS,
B.POS as FRM_T17_POS, 
A.TOS,
B.TOS as FRM_T17_TOS,
A.Specialty,
B.SPCLTY as FRM_T17_Specialty,
A.State,
B.ST_CD as FRM_T17_State,
A.AMT AS FRM_Addendum format dollar10.2,
B.AMT AS FRM_T17 format dollar10.2, 
A.AMT-B.AMT AS DIFF format dollar10.2,
A.EFFECTIVE_DATE1 format=mmddyy10.,
B.BEG AS Beg_T17 format=mmddyy10.,
B.TRM AS Trm_T17 format=mmddyy10.,
B.SYS_SETUP_DT AS T17_SETUP_DT format=mmddyy10.,
C.SYS_PRCD_CD_TRM as Termed_Procs format=mmddyy10. 

From 
pricing.Addendum_001 A
left outer join
pricing.t17_001 B
on
A.PROC = B.PROC
and
A.MOD1 = B.MOD1
and
A.MOD2 = B.MOD2

and 
case when a.Fee_Sch <> '' then
A.Fee_Sch = B.FS
else
A.Fee_Sch = ''
end

and
CASE WHEN A.TOS <> '' THEN
A.TOS = B.TOS
else A.TOS = ''
END

and
CASE WHEN A.STATE <> '' THEN
A.STATE = B.ST_CD
else A.STATE = ''
END

and
CASE WHEN A.Specialty <> '' THEN
A.Specialty = B.SPCLTY
else A.Specialty = ''
END

and
CASE WHEN A.POS <> '' THEN
A.POS = B.POS
else A.POS = ''
END

and 
A.EFFECTIVE_DATE1 < B.TRM
and
A.EFFECTIVE_DATE1 >= B.BEG


left outer join
pricing.TrmdCodes c
on
A.PROC = c.SYS_PRCD_CD

order by 
FS_T17,POS, A.Specialty

解决方案 #

Access的Jet SQL不支持在JOIN的ON子句中使用CASE语句返回布尔判断，但可以将原CASE逻辑转换为等价的AND/OR组合；同时原SQL中的格式语法、多表JOIN结构也需要调整以适配Access：

修改要点：

• 替换CASE条件：将CASE WHEN X <> '' THEN X=Y ELSE X='' END转换为(X <> '' AND X=Y) OR X=''，简化后等价于X='' OR X=Y
• 格式函数替换：Access使用Format()函数处理字段格式化，例如金额格式Format(字段, "$#,##0.00")，日期格式Format(字段, "mm/dd/yyyy")
• 多表JOIN结构：Access要求多表JOIN时需用括号包裹前序JOIN结果，避免语法错误
• 修正ORDER BY字段：原SQL中FS_T17字段不存在，调整为实际存在的字段（示例中改为A.Fee_Sch）

适配Access的SQL代码：

SELECT
    A.PROC,
    A.MOD1,
    A.MOD2,
    A.POS,
    B.POS AS FRM_T17_POS,
    A.TOS,
    B.TOS AS FRM_T17_TOS,
    A.Specialty,
    B.SPCLTY AS FRM_T17_Specialty,
    A.State,
    B.ST_CD AS FRM_T17_State,
    Format(A.AMT, "$#,##0.00") AS FRM_Addendum,
    Format(B.AMT, "$#,##0.00") AS FRM_T17,
    Format(A.AMT - B.AMT, "$#,##0.00") AS DIFF,
    Format(A.EFFECTIVE_DATE1, "mm/dd/yyyy") AS EFFECTIVE_DATE1,
    Format(B.BEG, "mm/dd/yyyy") AS Beg_T17,
    Format(B.TRM, "mm/dd/yyyy") AS Trm_T17,
    Format(B.SYS_SETUP_DT, "mm/dd/yyyy") AS T17_SETUP_DT,
    Format(C.SYS_PRCD_CD_TRM, "mm/dd/yyyy") AS Termed_Procs
FROM
    (pricing.Addendum_001 A
LEFT JOIN
    pricing.t17_001 B
ON
    A.PROC = B.PROC
    AND A.MOD1 = B.MOD1
    AND A.MOD2 = B.MOD2
    AND (A.Fee_Sch = '' OR A.Fee_Sch = B.FS)
    AND (A.TOS = '' OR A.TOS = B.TOS)
    AND (A.State = '' OR A.State = B.ST_CD)
    AND (A.Specialty = '' OR A.Specialty = B.SPCLTY)
    AND (A.POS = '' OR A.POS = B.POS)
    AND A.EFFECTIVE_DATE1 < B.TRM
    AND A.EFFECTIVE_DATE1 >= B.BEG)
LEFT JOIN
    pricing.TrmdCodes C
ON
    A.PROC = C.SYS_PRCD_CD
ORDER BY
    A.Fee_Sch, A.POS, A.Specialty

注意事项 #

• 如果A.Fee_Sch等字段存储的是NULL而非空字符串，需将判断条件改为A.Fee_Sch IS NULL OR A.Fee_Sch = B.FS
• 日期格式可根据需求调整，例如"mmddyyyy"对应无分隔符的日期显示

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