寻求Microsoft Access多表场景下引用当前记录的Count Distinct替代方案

由于Microsoft Access不支持COUNT(DISTINCT)直接嵌套在聚合查询中，你可以通过以下两种方案实现统计每个咨询师有预约的唯一客户数：

方案一：嵌入相关子查询 #

直接在原查询的SELECT子句中添加一个相关子查询，该子查询会针对当前行的咨询师ID，统计其有预约的唯一客户数量：

SELECT 
    Counsellors.Counsellor_Name,
    -- 新增的唯一客户统计列
    (SELECT COUNT(*) 
     FROM (SELECT DISTINCT Clients.ID 
           FROM Clients 
           INNER JOIN Appointments ON Clients.ID = Appointments.Client 
           WHERE Clients.Counsellor = Counsellors.ID) AS SubQuery) AS UniqueClientsInDateRange,
    Count(IIf(Clients.Client_Type = 'Single Adult', Appointments.ID, NULL)) AS AppointmentsSingleAdult,
    Count(Appointments.ID) AS TotalAppointments,
    Count(IIf(Appointments.Canceled_By_Staff = TRUE, Appointments.ID, NULL)) AS CancelByStaff,
    Count(IIf(Appointments.Canceled_Or_No_Show = '1' AND Appointments.Canceled_By_Staff = FALSE, Appointments.ID, NULL)) AS CancelByClient,
    Count(IIf(Appointments.Canceled_Or_No_Show = 'ns' AND Clients.NSP_Rate = 0 AND Appointments.Canceled_By_Staff = FALSE, Appointments.ID, NULL)) AS NSByClient,
    Count(IIf(Appointments.Canceled_Or_No_Show = 'ns' AND Clients.NSP_Rate <> 0 AND Appointments.Canceled_By_Staff = FALSE, Appointments.ID, NULL)) AS NSPaidByClient,
    Count(IIf(Appointments.BC = TRUE, Appointments.ID, NULL)) AS BCCount
FROM
    Counsellors INNER JOIN (Clients INNER JOIN Appointments ON Clients.ID = Appointments.Client) ON Counsellors.ID = Clients.Counsellor
GROUP BY
    Counsellors.Counsellor_Name;

如果需要添加日期范围过滤，只需在子查询的WHERE子句中补充条件：

WHERE Clients.Counsellor = Counsellors.ID 
AND Appointments.Appointment_Date BETWEEN #2024-01-01# AND #2024-12-31#

方案二：预聚合子查询关联（效率更优） #

先预先生成每个咨询师对应的有预约唯一客户数，再将该结果与原查询关联，适合数据量较大的场景：

SELECT 
    Counsellors.Counsellor_Name,
    SubStats.UniqueClients AS UniqueClientsInDateRange,
    Count(IIf(Clients.Client_Type = 'Single Adult', Appointments.ID, NULL)) AS AppointmentsSingleAdult,
    Count(Appointments.ID) AS TotalAppointments,
    Count(IIf(Appointments.Canceled_By_Staff = TRUE, Appointments.ID, NULL)) AS CancelByStaff,
    Count(IIf(Appointments.Canceled_Or_No_Show = '1' AND Appointments.Canceled_By_Staff = FALSE, Appointments.ID, NULL)) AS CancelByClient,
    Count(IIf(Appointments.Canceled_Or_No_Show = 'ns' AND Clients.NSP_Rate = 0 AND Appointments.Canceled_By_Staff = FALSE, Appointments.ID, NULL)) AS NSByClient,
    Count(IIf(Appointments.Canceled_Or_No_Show = 'ns' AND Clients.NSP_Rate <> 0 AND Appointments.Canceled_By_Staff = FALSE, Appointments.ID, NULL)) AS NSPaidByClient,
    Count(IIf(Appointments.BC = TRUE, Appointments.ID, NULL)) AS BCCount
FROM
    Counsellors 
INNER JOIN (Clients INNER JOIN Appointments ON Clients.ID = Appointments.Client) ON Counsellors.ID = Clients.Counsellor
LEFT JOIN (
    -- 预聚合每个咨询师的有预约唯一客户数
    SELECT 
        Clients.Counsellor,
        COUNT(DISTINCT Clients.ID) AS UniqueClients
    FROM Clients
    INNER JOIN Appointments ON Clients.ID = Appointments.Client
    -- 这里添加日期范围条件
    -- WHERE Appointments.Appointment_Date BETWEEN #2024-01-01# AND #2024-12-31#
    GROUP BY Clients.Counsellor
) AS SubStats ON Counsellors.ID = SubStats.Counsellor
GROUP BY
    Counsellors.Counsellor_Name, SubStats.UniqueClients;

关键说明 #

两种方案的核心都是通过子查询关联外层的Counsellors.ID，解决了你之前子查询无法引用当前咨询师ID的问题。预聚合方案的优势在于将唯一客户统计逻辑提前计算，避免了每行重复执行子查询，性能更优。

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