Access编写两表关联查询列出全部员工及指定日期自评提交记录

问题根源 #

当前查询的核心问题是将Assessment表的Date Signed字段筛选条件写在了WHERE子句中。左连接生成的未提交自评员工的Date Signed值为NULL，不满足日期区间判断条件，会被直接过滤，最终仅能展示提交过自评的员工记录，相当于LEFT JOIN被变相转为了INNER JOIN。

修正后的查询代码 #

将日期筛选条件移到LEFT JOIN的关联条件中即可，该写法只会过滤Assessment表的匹配记录，不会排除未提交自评的员工：

SELECT [Employee Info].ID, [Employee Info].[Employee Name], [Employee Info].[Employee Number], Assessment.[Date Signed]
FROM [Employee Info] LEFT JOIN Assessment 
ON (
  [Employee Info].[Employee Number] = Assessment.[Employee Number] 
  AND Assessment.[Date Signed] >= [Start Date] 
  AND Assessment.[Date Signed] <= [End Date]
)
ORDER BY Assessment.[Date Signed] DESC, [Employee Info].[Employee Number];

优化建议 #

• 关联条件优先使用Employee Number（工号）而非姓名，可避免员工重名导致的关联匹配错误
• 若仅需要查询当日的自评情况，可直接把日期条件替换为Assessment.[Date Signed] = DATE()，无需手动输入起止日期参数

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