无法在 Access 查询中进行运行总和 [英] Trouble making a running sum in Access query
问题描述
我一直在尝试获取运行总和以在 Access 查询中工作.我一直在玩各种 Dsum 表达式,但它们都导致了错误.基本上,我有两列,一个是年份,一个是那一年的零件数量,我希望第三个是多年来零件数量的运行总和.
I'm stuck trying to get a running sum to work in an Access query. I've been playing around with various Dsum expressions, but they all have resulted in errors. Basically, I have two columns, one with a year, one with a count of parts for that year, and I would like the third to be a running sum of the part count over the years.
前两列的 SQL 如下所示:
My SQL for the first two columns looks like this:
SELECT DatePart("yyyy",[EoL]) AS AYear, Count(EquipmentQuery.Equipment) AS EquipCount
FROM EquipmentQuery
GROUP BY DatePart("yyyy",[EoL])
ORDER BY DatePart("yyyy",[EoL]);
关于如何让第三列作为运行总和工作的任何建议?感谢您的帮助!
Any suggestions on how to get the third column to work as a running sum? Thanks for the help!
推荐答案
如果您创建报告,则有一个 计算运行总和的属性.
If you create a report, there is a property to calculate a running sum.
如果您更喜欢查询,可以使用子查询来计算运行总和:
If you prefer a query, you can use a subquery to calculate the running sum:
SELECT DatePart("yyyy",[EoL]) AS AYear
, Count(eq1.Equipment) AS EquipCount
, (
SELECT Count(eq2.Equipment)
FROM EquipmentQuery eq2
WHERE DatePart("yyyy",eq2.[EoL]) <= DatePart("yyyy",eq1.[EoL])
) AS RunningSuma
FROM EquipmentQuery AS eq1
GROUP BY
DatePart("yyyy",[EoL])
ORDER BY
DatePart("yyyy",[EoL]);
这篇关于无法在 Access 查询中进行运行总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!