根据日期范围运行总计 [英] Running total based on date range
问题描述
大家好，
我正在尝试根据要输入表单的特定日期范围创建运行总计。
我目前的表单设置有2个文本框（date1和date2），它们是我的开始和结束日期的输入，带有DATE字段和ORDPCK字段的表（tblALLBCPIC）（我需要的运行总和） ）。表中还有其他字段，但它们并不重要。
到目前为止，我的查询的SQL是：
SELECT tblALLBCPIC .DATE，tblALLBCPIC.ORDPCK，（SELECT Sum（[ORDPCK]）FROM [tblALLBCPIC] AS [tblALLBCPIC_1] WHERE [tblALLBCPIC_1]。[DATE]< = [tblALLBCPIC]。[DATE]）AS RunningSum
FROM tblALLBCPIC;
我添加并玩了各种配置，包括在[FORMS]之间添加
！[frmBCPIC] ！[date1]和[FORMS]！[frmBCPIC]！[date2]进入日期字段的条件。
我的结果通常会给我一个整个表的运行总计（ 2005-2007）而不仅仅是指定的日期范围。
据我所知，Dsum函数几乎可以做同样的事情，但需要花费更长的时间来计算。 ？
到目前为止失败的另一个可能因素是具有数据的表确实具有和NULL字段/条目。我知道Nz功能，我很确定这样的东西可以工作（Nz（[ORDPCK]，'0''））也许在某个地方。
任何帮助都会很大赞赏。如果您需要更多信息，请提前告知我们。
Hello all,
I am trying to create a running total based on a specific date range that is to be entered into a form.
I currently have the form set up with 2 text boxes (date1 and date2) which are the input for my start and end date, a table (tblALLBCPIC) with a DATE field and ORDPCK field (what I need a running sum of). There are other fields in the table but they shouldn?t matter.
Here is the SQL for my query so far:
SELECT tblALLBCPIC.DATE, tblALLBCPIC.ORDPCK, (SELECT Sum([ORDPCK]) FROM [tblALLBCPIC] AS [tblALLBCPIC_1] WHERE [tblALLBCPIC_1].[DATE] <= [tblALLBCPIC].[DATE]) AS RunningSum
FROM tblALLBCPIC;
I have added and played around with various configurations of this including adding
Between [FORMS]![frmBCPIC]![date1] And [FORMS]![frmBCPIC]![date2] into the criteria for the date field .
My results will usually give me a running total of the entire table (2005-2007) instead of for just the date range that was specified.
It is my understanding that the Dsum function will do pretty much the same thing but take much longer to calculate. ?
Another possible factor for failure so far is that the table with the data does have sum NULL fields/entries. I do know about the Nz function and am pretty sure something like this would work (Nz([ORDPCK],''0'')) maybe somewhere.
Any help would be greatly appreciated. If you require any more info please let me know, thanks in advance.
推荐答案
我在试图找出如何使这项工作时注意到的事情是，我正在使用的功能是通过我表中的每个记录来计算运行金额/总额。
也许我需要使用Between日期范围标准 -
[FORMS]之间！[frmBCPIC]！[date1]和[FORMS]！[frmBCPIC]！[date2]在运行的总函数中而不是在标准中。
我将尝试看看会发生什么。
Something I have noticed in trying to figure out how to make this work is, that the function I am using is going through every record in my table to make the running sum/total.
Perhaps I need to use the Between date range criteria -
Between [FORMS]![frmBCPIC]![date1] And [FORMS]![frmBCPIC]![date2] in the running total function instead of in the criteria.
I will give that a try and see what happens.
我在试图找出如何使这项工作时注意到的一点是，我正在使用的功能是通过我表中的每个记录来计算运行金额/总额。
也许我需要使用Between日期范围标准 -
。运行总函数中的[FORMS]！[frmBCPIC]！[date1]和[FORMS]！[frmBCPIC]！[date2]之间而不是标准。
我会尝试一下，看看会发生什么。
Something I have noticed in trying to figure out how to make this work is, that the function I am using is going through every record in my table to make the running sum/total.
Perhaps I need to use the Between date range criteria -
Between [FORMS]![frmBCPIC]![date1] And [FORMS]![frmBCPIC]![date2] in the running total function instead of in the criteria.
I will give that a try and see what happens.
给这个testrun
give this a testrun
感谢您抽出宝贵时间发表回复，非常感谢您的帮助。
Thank you for taking the time to post a response, your assistance is very much appreciated.
给这个测试人员
这篇关于根据日期范围运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
