在Union查询中使用带Where子句的聚合函数报错排查

云上老周

2026-7-27

解决方案

1. 直接修复语法错误

报错的核心原因是DAvg函数的WHERE条件未正确处理文本类型字段。如果Access_ID是文本型，拼接条件时必须用单引号包裹字段值，否则Access会识别为无效表达式。

修改后的问题源查询SQL：

SELECT 
    Qry_TimeCollection.Access_ID,
    Format(Nz([Historical_Run_Avg1],"0"),"hh:nn:ss") AS Avg_Run_Time1,
    -- 为文本类型的Access_ID添加单引号
    DAvg("Historical_Run_1","Qry_TimeCollectionAvg","Access_ID = '" & [Access_ID] & "'") AS Historical_Run_Avg1,
    Qry_TimeCollection.Historical_Run_1
FROM Qry_TimeCollection;

若Access_ID是数值型，原写法本身合法，可跳过此修改，优先检查字段类型是否匹配。

2. 长期优化方案（适配业务需求）

考虑到你需要计算零件移动平均值并用于后续运算，预先将平均值存入物理表能避免嵌套函数带来的语法问题和性能损耗：

新建表Tbl_HistoricalAvg，包含字段：Access_ID、Part_Number、Historical_Avg_Run_Time
编写更新查询定期计算并存储平均值：

INSERT INTO Tbl_HistoricalAvg (Access_ID, Part_Number, Historical_Avg_Run_Time)
SELECT 
    tc.Access_ID,
    tc.Part_Number,
    Format(Nz(DAvg("Historical_Run_1","Qry_TimeCollection","Access_ID = '" & tc.Access_ID & "'"),"0"),"hh:nn:ss") AS Historical_Avg_Run_Time
FROM Qry_TimeCollection AS tc
GROUP BY tc.Access_ID, tc.Part_Number;