SQL Expresions using result of previous row of UNION ALL

Question

I have these two tables:

Collect: Date, Rute, Value_Collected...

Records: Date, Rute, Value, Type...

The other columns are not relevant in this case. So I have this working code:

SELECT

Totals.Date                             AS [Date],

Sum(Totals.Collected)                   AS [Collect],

Sum(Totals.Injected)                    AS [Injections],

Sum(Totals.Spent)                       AS [Expenses],

([Collect] + [Injections]) - [Expenses] AS [Total Day]



FROM (

    SELECT

    Records.Date                        AS [Date],

    0                                   AS [Collected],

    Sum(IIF(Type =  3,Records.Value,0)) AS [Injected],

    Sum(IIF(Type <> 3,Records.Value,0)) AS [Spent]

    FROM Records GROUP BY Records.Date



    UNION ALL



    SELECT

    Collect.Date                 AS [Date],

    Sum(Collect.Value_Collected) AS [Collected],

    0                            AS [Injected],

    0                            AS [Spent]

    FROM Collect GROUP BY Collect.Date

) AS Totals GROUP BY Totals.Date

And that give me this result:

|Date       |Collect    |Injections  |Expenses   |Total Day

-----------------------------------------------------------

|5/09/2016  |$47.000   |$0           |$0         |$47.000 |

|6/09/2016  |$20.000   |$0           |$0         |$20.000 |

|7/09/2016  |$13.000   |$0           |$0         |$13.000 |

|11/09/2016 |$122.000  |$0           |$0         |$122.000|

|14/09/2016 |$7.000    |$0           |$0         |$7.000  |

|16/09/2016 |$100.000  |$0           |$7.000     |$93.000 |

|30/09/2016 |$0        |$80.000      |$65.000    |$15.000 |

-----------------------------------------------------------

And that is ok for now, but the formula of column

[Total Day]

must be calculated like this

([Total Day Of Last Row] + [Collect] + [Injections]) - [Expenses]

instead of

([Collect] + [Injections]) - [Expenses]

to give me this desired result:

|Date       |Collect      |Injections  |Expenses   |Total Day

-------------------------------------------------------------

|5/09/2016  |$47.000      |$0          |$0         |$47.000 |

|6/09/2016  |$20.000      |$0          |$0         |$67.000 |

|7/09/2016  |$13.000      |$0          |$0         |$80.000 |

|11/09/2016 |$122.000     |$0          |$0         |$202.000|

|14/09/2016 |$7.000       |$0          |$0         |$209.000|

|16/09/2016 |$100.000     |$0          |$7.000     |$302.000|

|30/09/2016 |$0           |$80.000     |$65.000    |$317.000|

-------------------------------------------------------------

But I dont know how to get the

[Total Day]

of the previous row for calculate the

[Total Day]

of current row. If I try to run a subquery over

[Totals]

, it gives me an error because it cannot find the table

Totals

. I think it is because it is not a "real" table, but really I don't know how to achive this. I really appreciate any help.

PS: the names of the columns and tables are originally in Spanish but translated for purposes so that you could understand what I need to accomplish.

Answer 1

Essentially, you need a cumulative sum across the fields by progressive dates which can be handled with a correlated sum aggregate subquery. The only challenge is that MS Access does not have window functions like other SQL dialects to reuse derived table queries. So you need to repeat the union derived table inside the subquery:

SELECT main.Date, main.Collected AS [Collect], 
       main.Injected As [Injections], main.Spent As [Expenses],
       Ccur((SELECT Sum(sub.Collected) + Sum(sub.Injected) - Sum(sub.Spent) 
             FROM 
               (SELECT Records.Date AS [Date], 0 AS [Collected],
                       Sum(IIF(Type =  3,Records.Value,0)) AS [Injected],
                       Sum(IIF(Type <> 3,Records.Value,0)) AS [Spent]
                FROM Records GROUP BY Records.Date
                UNION ALL
                SELECT Collect.Date AS [Date],
                       Sum(Collect.Value_Collected) AS [Collected],
                       0 AS [Injected],
                       0 AS [Spent]
                FROM Collect GROUP BY Collect.Date
                ) As sub
             WHERE sub.Date <= main.Date)) As [Total Day]

FROM
  (SELECT Records.Date AS [Date], 0 AS [Collected],
          Sum(IIF(Type =  3,Records.Value,0)) AS [Injected],
          Sum(IIF(Type <> 3,Records.Value,0)) AS [Spent]
   FROM Records GROUP BY Records.Date
   UNION ALL
   SELECT Collect.Date AS [Date],
         Sum(Collect.Value_Collected) AS [Collected],
         0 AS [Injected],
         0 AS [Spent]
   FROM Collect GROUP BY Collect.Date
  ) As main

Fortunately, MS Access does maintain the stored query object where you save the UNION query as a separate query and reference it in new query for a shorter script:

SELECT main.Date, main.Collected As [Collect], 
       main.Injected As Injections, main.Spent As Expenses,  
       Ccur((SELECT Sum(sub.Collected) + Sum(sub.Injected) - Sum(sub.Spent) 
             FROM RecordsUnionQ As sub
             WHERE sub.Date <= main.Date)) As [Total Day]
FROM RecordsUnionQ As main

SQL Expresions using result of previous row of UNION ALL

Sign up