I have these two tables:
Collect: Date, Rute, Value_Collected...
Records: Date, Rute, Value, Type...
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
|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 |
-----------------------------------------------------------
[Total Day]
([Total Day Of Last Row] + [Collect] + [Injections]) - [Expenses]
([Collect] + [Injections]) - [Expenses]
|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|
-------------------------------------------------------------
[Total Day]
[Total Day]
[Totals]
Totals
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