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!
CodePudding user response：
Use a self LEFT join:
SELECT t1.*,
(t1.[Volume Balance] Nz(t2.[Volume Balance])) / 2 AS [Average Volume Balance]
FROM tablename AS t1 LEFT JOIN tablename AS t2
ON VAL(t2.Period) = VAL(t1.Period) - 1 AND t2.ID = t1.ID AND t2.Instrument = t1.Instrument;