含聚合函数与左连接的Access查询在Excel中结果异常的解决求助

问题重现 #

• 通过Access链接Snowflake中的SIMS_CLAIMANTLOG和SIMS_PAYMENT表，执行以下查询：

SELECT SIMS_CLAIMANTLOG.LOGID
    , Sum(IIf([processeddate] Is Not Null  And [reservetypeid] In ("2","3","4"),[AMOUNT],0)) AS IndPd
    , Sum(IIf([processeddate] Is Not Null  And [reservetypeid] In ("1","5"),[AMOUNT],0)) AS OtherPd
    , IIf([IndPd]>0,"Indem", IIf([OtherPd]>0,"MO","NoPay")) AS ClaimType
FROM SIMS_CLAIMANTLOG 
LEFT JOIN SIMS_PAYMENT ON SIMS_CLAIMANTLOG.PK = SIMS_PAYMENT.CLAIMANTID
WHERE SIMS_CLAIMANTLOG.ENTRYDATE>=Date()
GROUP BY SIMS_CLAIMANTLOG.LOGID;

• 该查询在Access内返回正确结果，但通过Excel「获取数据-来自数据库-来自Access」连接后，左连接下所有记录的ClaimType均显示为NoPay，IndPd和OtherPd全为0：

LOGID IndPd OtherPd ClaimType 
17991679 0 0 NoPay 
17991680 0 0 NoPay 
17991681 0 0 NoPay 
17991682 0 0 NoPay 

• 改为内连接后，Excel和Access结果一致，显示正确数据：

LOGID IndPd OtherPd ClaimType 
18008782 4576.05 1107.4 Indem 
18008783 0 3.15 MO 
18008790 0 0 NoPay 

• 补充细节：Snowflake中reservetypeid为数值类型，Access链接表中显示为短文本；尝试用Nz函数处理空值，Access内正常，但该查询无法在Excel的Access查询列表中显示。

问题根源 #

Excel通过Access链接查询时，对Access专属的IIf函数在左连接空值场景下的解析存在兼容性问题，导致Sum(IIf(...))无法正确计算非空匹配的金额，最终IndPd和OtherPd被错误置为0，进而ClaimType全部判定为NoPay。另外，Nz函数属于Access私有函数，Excel外部数据连接无法识别，导致使用该函数的查询无法被加载。

解决方案 #

方案1：改用标准SQL兼容的CASE表达式替代IIf #

使用所有SQL环境通用的CASE语句替换Access专属的IIf，同时避免在SELECT中引用同语句的字段别名（部分外部连接不支持该特性）：

SELECT SIMS_CLAIMANTLOG.LOGID
    , Sum(CASE 
        WHEN processeddate IS NOT NULL AND reservetypeid IN ('2','3','4') THEN AMOUNT 
        ELSE 0 
    END) AS IndPd
    , Sum(CASE 
        WHEN processeddate IS NOT NULL AND reservetypeid IN ('1','5') THEN AMOUNT 
        ELSE 0 
    END) AS OtherPd
    , CASE 
        WHEN Sum(CASE 
            WHEN processeddate IS NOT NULL AND reservetypeid IN ('2','3','4') THEN AMOUNT 
            ELSE 0 
        END) > 0 THEN 'Indem'
        WHEN Sum(CASE 
            WHEN processeddate IS NOT NULL AND reservetypeid IN ('1','5') THEN AMOUNT 
            ELSE 0 
        END) > 0 THEN 'MO'
        ELSE 'NoPay'
    END AS ClaimType
FROM SIMS_CLAIMANTLOG 
LEFT JOIN SIMS_PAYMENT ON SIMS_CLAIMANTLOG.PK = SIMS_PAYMENT.CLAIMANTID
WHERE SIMS_CLAIMANTLOG.ENTRYDATE >= Date()
GROUP BY SIMS_CLAIMANTLOG.LOGID;

方案2：修复数据类型不一致问题 #

由于Snowflake中reservetypeid为数值类型，Access链接后转为短文本，可在查询中显式转换类型，避免匹配异常：
将reservetypeid IN ('2','3','4')改为CAST(reservetypeid AS INTEGER) IN (2,3,4)，同理('1','5')改为(1,5)，确保类型匹配准确。

方案3：在Snowflake端预定义视图 #

将查询逻辑直接在Snowflake中创建为视图，再通过Access链接该视图，最后让Excel连接Access中的视图。这种方式将计算逻辑放在Snowflake端执行，彻底规避Access与Excel的函数兼容性问题。

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