Search code examples
sqlms-access

Subquery in access: how to get previous row calculated value

I've table which is a query result, with added calculation fields in X to get the net value. Simplifying it, it would look like this:

period_start | period_end | gross | X | net |
|:----------:|:----------:|:-----:|:-:| ---:|
| 31.12.2007 | 31.12.2008 | 3000  |30 |2970 |
| 31.12.2008 | 30.11.2020 | 3000  |50 |2950 |
| 30.11.2020 | 30.07.2054 | 3000  |150|2850 |

How could I modify it to connect period_start with period_end, to show gross as previous month's net, using ms-access query? I've tried subqueries before to get previous period value, but never in the middle of the calculation. Best result would be:

period_start | period_end | gross | X | net |
|:----------:|:----------:|:-----:|:-:| ---:|
| 31.12.2007 | 31.12.2008 | 3000  |30 |2970 |
| 31.12.2008 | 30.11.2020 | 2970  |50 |2920 |
| 30.11.2020 | 30.07.2054 | 2920  |150|2770 |
Solution

  • Use the query result in another query. Consider:

    SELECT Data.*, 
gross - Nz((SELECT Sum(x) AS SX FROM Data AS Dupe WHERE Dupe.Period_end<Data.Period_end ),0) AS AdjGross, 
gross - (SELECT Sum(x) AS SX FROM Data AS Dupe WHERE Dupe.Period_end<=Data.Period_end) AS AdjNet
FROM Data;

    Possible alternative is to build a report based on your query and use textbox RunningSum property.