1

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!

forpas
  • 160,666
  • 10
  • 38
  • 76
DVEF
  • 47
  • 4
  • OPTION 1) Use an iif to evaluate if period in (period OR period - 1 Month) then volume balance; else 0)/2 so volume balance will be populated twice 0 otherwise and then divide by 2... OPTION 2) perform a self outer join on period and period -1 (using SQL). then average. and group by the period on your main table. You may need to convert your date to a "Date" object to do the date math and then render back to formatted text of YYYYMM – xQbert Feb 11 '22 at 16:20

1 Answers1

0

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;
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Hi forpas, this worked perfectly! Thank you for the quick help. – DVEF Feb 14 '22 at 08:26
  • Just one quick follow-up question. Indeed, I have a slightly more complex table where I have two other inputs "ID number" and "Instrument", both short texts. Accordingly, my table has the same period multiple times with different amounts per IDs and Instruments. When I run the above, I think access is computing a Cartesian product as the number of records I get is very large. I tried grouping by ID and instrument, but this is only giving me errors. Any guidance as to how to group this by ID and instrument? – DVEF Feb 14 '22 at 10:26
  • @DVEF This query works only if Period is unique, just like your sample data. If Period is not unique the rows are joined multiple times. Since this is a new requirement you should ask a new question, with sample data where you will clarify your expected results. – forpas Feb 14 '22 at 10:34
  • Understood, I will make it a new question - thanks! – DVEF Feb 14 '22 at 10:43