Microsoft Access求和查询如何包含总已开票工时为0的员工记录？

核心问题 #

原有查询使用INNER JOIN加WHERE子句筛选已开票状态，仅保留两张表中能完全匹配的记录，因此不会返回无已开票工时的员工。

调整逻辑 #

• 先提取全量员工名单作为主表，保证所有需要统计的员工都能出现在结果集中。如果你的系统中有单独的员工基础表，直接替换子查询(SELECT DISTINCT Worker FROM [EYH Export])为员工表即可，统计准确性更高。
• 将已开票状态的筛选从WHERE子句移动到关联条件中，避免左连接后筛选掉无匹配记录的员工。
• 使用Access内置的Nz函数，将无已开票工时的员工汇总空值转换为0。

修改后可直接运行的SQL #

SELECT 
    AllWorkers.Worker, 
    Nz(Sum([EYH Export].Hours), 0) AS [Billed Hours]
FROM 
    -- 提取全量员工名单作为主表
    (SELECT DISTINCT Worker FROM [EYH Export]) AS AllWorkers
LEFT JOIN 
    -- 先关联得到所有已开票的工时记录
    ([EYH Export] INNER JOIN [Job List] 
     ON [EYH Export].Client = [Job List].Client 
     AND [Job List].Billed = "Billed")
-- 关联员工和对应已开票工时
ON AllWorkers.Worker = [EYH Export].Worker
GROUP BY AllWorkers.Worker;

兼容写法（适配部分低版本Access设计视图打开报错的情况） #

SELECT 
    AllWorkers.Worker, 
    Nz(Sum([EYH Export].Hours), 0) AS [Billed Hours]
FROM 
    (SELECT DISTINCT Worker FROM [EYH Export]) AS AllWorkers
LEFT JOIN 
    [EYH Export] ON AllWorkers.Worker = [EYH Export].Worker
LEFT JOIN
    (SELECT Client FROM [Job List] WHERE Billed = "Billed") AS BilledJobs
    ON [EYH Export].Client = BilledJobs.Client
GROUP BY AllWorkers.Worker;

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