CODEWITHSUNDEEP
sqlms-access
MichaelG
MichaelG

Reputation: 9

Running total query in access

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

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.

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

The code to create this is:

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];

Your assistance is appreciated.

Upvotes: 0

Views: 683

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 1

MichaelG
MichaelG

Reputation: 9

thank you for your responses... With what you gave me, I made a couple mods and was able to make it work... here is the final answer:

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

FROM Trades AS T1

WHERE (((T1.[Entry time]) Between [Start Date] And DateAdd("d",1,[End Date])))
ORDER BY T1.[Entry time];

Thanks again

Upvotes: 0

Related Questions