Thank you, in advance for your help with this.
I have a monthly currency table. I am looking to update the 5th column "YTD Avg" rate column with a running average based on Month End Rate. So Month 2 "YTD Avg" is avg for month 1&2, Month 3 YTD Avg is the avg for months 1,2,and 3, Month 4 YTD Avg is the average of months 1-4 and so on.
I'm at a loss on how to write the update statement in Access 365. I created a duplicate table FX2. The standalone select statement below seems to work, but I can't convert it to the update statement
SELECT FX2.Year, FX2.Month, FX2.Currency, avg([FX1 Rates].[Month End Rate])
FROM FX2 INNER JOIN [FX1] ON (FX2.Currency = [FX1].Currency) AND (FX2.Year = [FX1].Year) AND (FX2.month >= [FX1].month)
where FX2.YEAR =2020
and FX2.CURRENCY= "AUD"
GROUP BY FX2.Year, FX2.Month, FX2.Currency
Year Month Currency Month End Rate YTD Avg 2020 1 AUD 100 100 2020 2 AUD 110 105 2020 3 AUD 120 110 2020 4 AUD 130 115 2020 5 AUD 140 120 2020 6 AUD 150 125