I have a table in an access database with the following fields: "Period" (as YYYYMM, so e.g. 202111, 202112, 202201 and so on), ID (a 'short text field' identifier), Instrument (a 'short text field' describing an instrument ID key) and, lastly, a dollar amount as field "Volume Balance". Clearly, in reality, there are many more IDs and different Instrument keys than just 3, resp. 2, as in the example below. The goal is now to calculate a simple average for every two consecutive months like the bottom table (exemplary numbers, unit is USD) per ID and per instrument. That is to say, one ID can have multiple entries in the same month for different Instrument keys (in the below table, ID 1 and 3 have balances in two different instruments A and B, while ID 2 only has a balance in instrument B).
Input -->
| Period | ID | Instrument | Volume Balance |
|---|---|---|---|
| 202101 | 1 | A | 1 |
| 202101 | 2 | B | 2 |
| 202101 | 3 | A | 3 |
| 202102 | 1 | A | 4 |
| 202102 | 1 | B | 5 |
| 202102 | 2 | B | 6 |
| 202102 | 3 | A | 7 |
| 202103 | 1 | A | 8 |
| 202103 | 2 | B | 9 |
| 202103 | 3 | A | 10 |
| 202103 | 3 | B | 11 |
Desired Result (essentially a glamorized Pivot table) -->
| Period | ID | Instrument | Average Volume Balance | Comment |
|---|---|---|---|---|
| 202101 | 1 | A | 0.5 | (Jan balance / 2) since no prior data point for Instrument & ID combination |
| 202101 | 2 | B | 1 | (Jan balance / 2) since no prior data point for Instrument & ID combination |
| 202101 | 3 | A | 1.5 | (Jan balance / 2) since no prior data point for Instrument & ID combination |
| 202102 | 1 | A | 2.5 | (Jan + Feb balance for this specific ID & Instrument combo / 2) |
| 202102 | 1 | B | 2.5 | (Feb balance / 2) since no prior data point for Instrument & ID combination |
| 202102 | 2 | B | 4 | (Jan + Feb balance for this specific ID & Instrument combo / 2) |
| 202102 | 3 | A | 5 | (Jan + Feb balance for this specific ID & Instrument combo / 2) |
| 202103 | 1 | A | 6 | (Feb + Mar balance for this specific ID & Instrument combo / 2) |
| 202103 | 2 | B | 7.5 | (Jan + Feb balance for this specific ID & Instrument combo / 2) |
| 202103 | 3 | A | 8.5 | (Jan + Feb balance for this specific ID & Instrument combo / 2) |
| 202103 | 3 | B | 5.5 | (Mar balance / 2) since no prior data point for Instrument & ID combination |
I have played around with the DAvg function but the consecutive period cut-offs and grouping are fatal for me. Thank you for any help!
Use a self LEFT join:
SELECT t1.*,
(t1.[Volume Balance] + Nz(t2.[Volume Balance])) / 2 AS [Average Volume Balance]
FROM (
SELECT * FROM tablename
UNION ALL
SELECT Format(DateAdd('m', 1, CDate(Format(MAX(Period) + '01', '0000-00-00'))), 'yyyymm'), ID, Instrument, 0
FROM tablename
GROUP BY ID, Instrument
) AS t1 LEFT JOIN tablename AS t2
ON DateAdd('m', 1, CDate(Format(t2.Period + '01', '0000-00-00'))) = CDate(Format(t1.Period + '01', '0000-00-00'))
AND t2.ID = t1.ID AND t2.Instrument = t1.Instrument
ORDER BY t1.ID, t1.Instrument, t1.Period;