在访问中运行总查询 [英] Running total query in access

问题描述

一段时间以来，我一直在尝试解决此问题，但没有成功.我不是一个出色的Sql编码器，所以我去找专家...我正在使用Access2010.我有一个包含以下字段的数据库(交易):

I have been trying to solve this for a while now without success. I'm not a great Sql coder so I've come to the experts... I'm using Access 2010. I have a database (Trades) with the following fields:

    Entry time   -- Date/Time
    Profit       -- Number

我想基于日期(例如，2016年7月1日至2016年8月1日)提取记录的子集，并且此提取物中的运行利润总额为连续收入.

I want to extract a subset of records based on date (e.g. 7/1/2016 through 8/1/2016) and within this extract have a running total of Profit.

以下查询使我关闭，但输出显示我在Trades中的所有记录，并且对我想要的时间段进行了总计.对于开始时间之前的记录，运行总计为空白.对于在结束时间之后显示的记录，运行总计与为结束时间计算的结果相同.输出看起来像时间段为7/26-7/28:

The following query gets me close but the output shows me all records in Trades and does the running total for the time period I want. For the records prior to the start time, the running total is blank. For records displayed after the end time, the running total is the same as what was calculated for the end time. Output looks like for the time period being 7/26 - 7/28:

    Entry time            Profit    Total
    7/14/2016 9:39:51 AM    2   
    7/14/2016 9:48:26 AM    0.25    
    7/26/2016 10:06:04 AM   -0.75   -0.75
    7/26/2016 10:29:29 AM   -1.25   -2
    7/26/2016 11:03:51 AM   2        0
    7/27/2016 9:29:52 AM    0.5      0.5
    7/27/2016 9:52:51 AM    -1.25   -0.75
    7/27/2016 10:01:56 AM   -0.75   -1.5
    7/28/2016 9:44:25 AM    -1.25   -2.75
    7/28/2016 10:44:15 AM   0.25    -2.5
    8/1/2016 9:46:43 AM      0.5    -2.5
    8/1/2016 10:02:00 AM    2       -2.5

创建此代码的代码是:

SELECT T1.[Entry time], T1.Profit, 
 (SELECT Sum(Trades.[Profit]) AS Total

    FROM Trades

    WHERE (Trades.[Entry time] Between CDate([Start Date]) And
   DateAdd("d",1,CDate([End Date]))) AND  
   (Trades.[Entry time]) <= T1.[Entry time]) AS Total


FROM Trades AS T1


ORDER BY T1.[Entry time];

感谢您的帮助.

推荐答案

您只需要在子查询和外部查询中都包括日期条件:

You just need to include the date criteria in both the subquery and the outer query:

SELECT T1.[Entry time], T1.Profit, 
       (SELECT Sum(Trades.[Profit]) AS Total
        FROM Trades as t2
        WHERE t2.[Entry time] Between t2.[Start Date] And
   DateAdd("d", 1, t2.[End Date]) AND 
               (t2.[Entry time]) <= T1.[Entry time]
       ) AS Total
FROM Trades AS T1
WHERE t1.[Entry time] Between t1.[Start Date] And
                              DateAdd("d", 1, t1.[End Date])
ORDER BY T1.[Entry time];

请注意，我限定了所有列名(包括表引用).我认为这始终很重要，但在使用相关子查询时更是如此.

Notice that I qualified all the column names (included the table reference). I think this is always important, but even more so when using correlated subqueries.

这篇关于在访问中运行总查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！