MS Access查询求和返回空值，添加Nz()后仍显示0的问题求助

你的核心问题出在基础查询的汇总逻辑错误和双字段关联的匹配误差，以下是分步修复方案：

1. 修复Quantity_Query的汇总逻辑 #

你当前的Quantity_Query只是按所有字段分组，并未真正实现"汇总数量"的需求，会生成大量重复的零件行，导致后续关联逻辑混乱。修改为按零件号+描述汇总数量：

SELECT 
  r_22to24.or_part_num, 
  r_22to24.or_part_desc, 
  Sum(r_22to24.ol_quantity) AS SumOfol_quantity
FROM r_22to24
GROUP BY r_22to24.or_part_num, r_22to24.or_part_desc;

2. 先汇总工时数据再关联 #

直接用未汇总的Per_Hours_Query关联会导致重复计算或匹配失败，先对工时数据按零件维度汇总：
可以新建查询Sum_Hours_Query，或者直接在关联查询中用子查询：

SELECT 
  part_num, 
  part_desc, 
  Sum(per_hours) AS Total_Hours
FROM Per_Hours_Query
GROUP BY part_num, part_desc;

3. 修正关联查询的匹配逻辑 #

你同时用part_num和part_desc关联，大概率是描述字段存在格式差异（比如前后空格、大小写、拼写细微差别），导致明明有工时数据却匹配失败，最终Nz()返回0。

修改后的关联查询SQL #

改用LEFT JOIN（逻辑和你原有的RIGHT JOIN一致，更直观），同时清理字段格式消除匹配误差：

SELECT 
  Q.or_part_num,
  Q.or_part_desc, 
  Nz(H.Total_Hours, 0) AS Hours,
  Q.SumOfol_quantity
FROM 
  Quantity_Query Q
  LEFT JOIN (
    SELECT 
      Trim(part_num) AS part_num,
      UCase(Trim(part_desc)) AS part_desc,
      Sum(per_hours) AS Total_Hours
    FROM Per_Hours_Query
    GROUP BY Trim(part_num), UCase(Trim(part_desc))
  ) H ON 
    Trim(Q.or_part_num) = H.part_num
    AND UCase(Trim(Q.or_part_desc)) = H.part_desc
WHERE 
  Q.or_part_num Like [Forms]![Part_Search]![PartNo_Search] & "*"
  AND Q.or_part_desc Like [Forms]![Part_Search]![PartDesc_Search] & "*"
ORDER BY Q.or_part_num;

• Trim()：去除字段前后空格
• UCase()：统一为大写，消除大小写差异

4. 排查剩余不匹配数据 #

如果仍有零件显示0，用以下查询找出不匹配的记录，手动核对数据差异：

SELECT 
  Q.or_part_num,
  Q.or_part_desc,
  H.part_num,
  H.part_desc
FROM Quantity_Query Q
LEFT JOIN Per_Hours_Query H ON 
  Trim(Q.or_part_num) = Trim(H.part_num)
WHERE H.part_num IS NULL;

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