I have a table in an access database with essentially two critical fields: "Period" (as YYYYMM, so e.g. 202111, 202112, 202201 and so on) and, further, a dollar amount as field "Volume Balance". The goal is now to calculate a simple average for every two consecutive months like the bottom table (exemplary numbers, unit is USD):
Input -->
| Period | Volume Balance |
|---|---|
| 202101 | 1 |
| 202102 | 2 |
| 202103 | 3 |
Desired Result -->
| Period | Average Volume Balance |
|---|---|
| 202102 | 1.5 ((Jan+Feb)/2) |
| 202103 | 2.5 ((Feb+Mar)/2) |
I have played around with the DAvg function but the consecutive period cut-offs are fatal for me. Thank you for any help!
You need a self join:
SELECT t1.Period,
(t1.[Volume Balance] + t2.[Volume Balance]) / 2 AS [Average Volume Balance]
FROM tablename AS t1 INNER JOIN tablename AS t2
ON VAL(t1.Period) = VAL(t2.Period) + 1;
If the data type of Period is a numeric type then change the ON clause to just:
ON t1.Period = t2.Period + 1;