Access中基于双条件筛选待付款销售记录的查询问题

问题根源分析 #

你的现有查询存在两个核心问题：

• JOIN方向错误：使用RIGHT JOIN [Unpaid Hold Reasons]会强制包含Unpaid Hold Reasons表的所有记录，即使没有对应销售记录，这不仅导致不符合要求的Unpaid Hold状态（如"Incomplete - Payment Issue"）被保留，还错误地让[Unpd Hd?]列全显示"YES"。
• 缺少状态筛选逻辑：WHERE子句仅过滤了付款日期，未应用你要求的两个状态规则，导致不符合条件的记录漏网。

修正后的查询语句 #

SELECT 
    [BLUE New Sales TBL].AppID, 
    [BLUE New Sales TBL].Business, 
    [BLUE New Sales TBL].[Sales Rep], 
    [BLUE New Sales TBL].Created, 
    [BLUE New Sales TBL].Status, 
    IIf([Reversal Reasons].ID Is Not Null, "YES", "NO") AS [Reversal?], 
    IIf([Unpaid Hold Reasons].ID Is Not Null, "YES", "NO") AS [Unpd Hd?], 
    [BLUE New Sales TBL].[Last Update], 
    [BLUE New Sales TBL].[Date Pd], 
    [BLUE New Sales TBL].[Date Rev], 
    [BLUE New Sales TBL].[Hold Rev], 
    [BLUE New Sales TBL].[Date Mgr Pd], 
    [BLUE New Sales TBL].[Date Mgr Rev]
FROM 
    [BLUE New Sales TBL]
    LEFT JOIN [Reversal Reasons] ON [BLUE New Sales TBL].Status = [Reversal Reasons].Status
    LEFT JOIN [Unpaid Hold Reasons] ON [BLUE New Sales TBL].Status = [Unpaid Hold Reasons].Status
WHERE 
    -- 匹配表单选择的付款日期
    [BLUE New Sales TBL].[Date Pd] = [Forms]![Form2]![Pay Date]
    -- 条件1：非Reversal状态，或Reversal状态且付款日期等于Rev Pd Date（假设Date Rev对应Rev Pd Date）
    AND (
        [BLUE New Sales TBL].Status NOT IN (SELECT Status FROM [Reversal Reasons])
        OR ([BLUE New Sales TBL].[Date Pd] = [BLUE New Sales TBL].[Date Rev])
    )
    -- 条件2：非Unpaid Hold状态，或Unpaid Hold状态且付款日期等于Unpd Hd Date（假设Hold Rev对应Unpd Hd Date）
    -- 若需完全排除Unpaid Hold状态，替换为：AND [BLUE New Sales TBL].Status NOT IN (SELECT Status FROM [Unpaid Hold Reasons])
    AND (
        [BLUE New Sales TBL].Status NOT IN (SELECT Status FROM [Unpaid Hold Reasons])
        OR ([BLUE New Sales TBL].[Date Pd] = [BLUE New Sales TBL].[Hold Rev])
    )
ORDER BY 
    [BLUE New Sales TBL].[Sales Rep], 
    [BLUE New Sales TBL].Created;

关键修改说明 #

1. JOIN逻辑调整：改用两次LEFT JOIN，以销售表为主表关联原因表，确保只有状态匹配时原因表的ID才非空，[Reversal?]和[Unpd Hd?]的判断才准确。
2. 补充状态筛选：在WHERE子句中加入两组条件，严格匹配你要求的状态规则；如果需要彻底排除Unpaid Hold状态，直接简化条件2即可。
3. 完善判断字段：给IIf补充"NO"默认值，避免空值显示，同时去掉冗余的表别名，提升可读性。

内容的提问来源于stack exchange，提问作者Ranie White