如何在MS Access表中创建使用聚合函数的计算列

有两种成熟的实现方案可以绕开Access更新查询不支持直接使用聚合函数的限制，操作如下：

方案1：基于临时聚合查询关联更新（数据量大时性能更优） #

1. 先将你编写的聚合分组查询保存为Access内置查询对象，命名为qry_completion_metric：

如果需要存储数值类型的指标而非格式化后的文本，建议去掉FORMAT函数，直接返回原始计算结果，避免后续数据转换问题

SELECT 
    PROMIS_LT_Long_ID.Short_ID,
    100 * (AVG(IIF(PROMIS_LT_Long_ID.Status = 'Complete', 1.0, 0))) AS completion_metric_query_result
FROM 
    PROMIS_LT 
INNER JOIN
    PROMIS_LT_Long_ID ON PROMIS_LT.SubjectID = PROMIS_LT_Long_ID.Short_ID
GROUP BY 
    PROMIS_LT_Long_ID.Short_ID;

2. 打开PROMIS_LT_Long_ID表的设计视图，新增completion_metric字段，字段类型选择数字（双精度），如果需要存储格式化后的文本则选择短文本。
3. 编写更新查询关联目标表和聚合查询完成字段赋值：

UPDATE PROMIS_LT_Long_ID
INNER JOIN qry_completion_metric 
ON PROMIS_LT_Long_ID.Short_ID = qry_completion_metric.Short_ID
SET PROMIS_LT_Long_ID.completion_metric = qry_completion_metric.completion_metric_query_result;

方案2：基于域聚合函数直接更新（适合小数据量场景，无需提前创建查询对象） #

直接使用Access内置的DAvg域聚合函数，在更新查询中直接完成计算赋值，SQL语句如下：

注意：如果Short_ID为数值类型，将条件中的单引号去掉，修改为"SubjectID = " & Short_ID

UPDATE PROMIS_LT_Long_ID
SET completion_metric = 100 * DAvg(
    "IIF(Status = 'Complete', 1.0, 0)",
    "PROMIS_LT",
    "SubjectID = '" & Short_ID & "'"
);

注意事项 #

• 如果业务需要指标随源数据实时更新，不建议将结果固化到表字段，直接关联原本的分组聚合查询做展示即可，避免数据不一致的问题
• Access 2010及以上版本支持表级计算列，可以直接在字段的表达式属性中填入对应DAvg计算逻辑实现自动更新，但数据量大时性能会有明显下降

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