按日期访问累计总数 [英] Access Cumulative Total by Date

问题描述

我需要按在线日期显示我们所有能源项目的累积功率.

I need to show the cumulative power of all our energy projects by their online date.

因此，如果一个项目在 2016 年上线，那么它也在 2017 年和 2018 年上线，应该计入这些总数中.我基本上需要一张表格来回答问题

So, if a project came online in 2016, it was also online in 2017 and 2018 and should be counted in those totals. I basically need a table that answers the questions

      "What was our energy capacity as of 12/31/2016, 12/31/2017,etc"

我可以通过对不同日期一遍又一遍地运行简单查询来实现，但理想情况下，我只运行一个查询并获取我需要的表.

I can do it by running a simple query over and over with different dates, but ideally I would run just one query and get the table I need.

我实际上不知道如何在 SQL 中编码，所以我只使用 Access 查询设计器，这可能会使事情变得更加困难.

I don't actually know how to code in SQL, so I just use the Access query designer, which might be making things more difficult.

得到我想要的简单查询:

Simple Query that gets what I want:

SELECT Sum(Projects.[System Size AC]) AS [SumOfSystem Size AC]
FROM Projects
WHERE (((Projects.[Online Date])<#1/1/2018#));

出现错误的查询:

SELECT DSum([Projects]![System Size AC],[Projects],Year([Online Date])<=[Yr]) AS [Run Sum], Year([Online Date]) AS Yr
FROM Projects
GROUP BY Year([Online Date]);

我可以通过对不同日期一遍又一遍地运行简单查询来实现，但理想情况下，我会得到一个表，其中包含在线日期列中的所有年份，就像当年可用的所有可用项目容量的累计总数

I can do it by running the simple query over and over with different dates, but ideally I would get one table with all the years in the online date column with like a cumulative total of all available project capacity available during that year

推荐答案

您可以使用相关子查询来做到这一点:

You can do this using a correlated subquery:

SELECT Year(p.[Online Date]) as yr,
       SUM(p.[System Size AC]) as this_year,
       (SELECT SUM(p2.[System Size AC])
        FROM Projects as p2
        WHERE YEAR(p2.[Online Date]) <= YEAR(p.[Online Date])
       ) as running_sum
FROM Projects as p
GROUP BY Year([Online Date]);

这篇关于按日期访问累计总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！