Calculation

[Perfac]

Attached there is an Excel spreadsheet. I may have a long story but in short, my challenge is to get the result in Access of the field marked in blue. I want a query that results in exactly the information as this spreadsheet. The rules for our budget follow below as well as the analysis for field E7 on the spreadsheet.

1. In this example the business started 1 Jan 2018. We want to budget on every Income and expense account. There is no problem with that, don’t waste time on that please. We start by having a t09Budget table which connect to every account through a foreign key BudItm_ID.
2. In a separate table t09InflationRate, there is a budgeted Inflation rate on the 1^st of Jan every following year.
3. We thought it right to create a table t09BudgetScenarios with fields including BudItm_ID, StartDate Enddate and BudAmount. This supplies the opening amount for every budget item, by creating one record at a time.
4. As can be seen on my spreadsheet, I would like to add the budgeted inflation every 1Jan to the amount on the previous December.
5. In field E7 on the spreadsheet it can be seen that the 50 is added permanently to the series of amounts that follows. The 1000 is added once off and not included in the following months numbers. These two fields are in t09BudgetScenarios and the dates help to get it right.
6. My “Access” challenge is to find the previous months budget amount, to be able to do all the calculations to return the current month’s amount.

[Perfac]

I would like to make it easier. I simply need to learn how to find the amount in the same field of the previous month to calculate in this month. Someone went through all the effort and showed me a way some months ago. I downloaded the discussion, and didn't give attention and now I fail to find it.

[June7]

See if this helps http://allenbrowne.com/subquery-01.html#AnotherRecord

[accesstos]

I would like to make it easier. I simply need to learn how to find the amount in the same field of the previous month to calculate in this month. Someone went through all the effort and showed me a way some months ago. I downloaded the discussion, and didn't give attention and now I fail to find it.

I think that you are talking about this thread: https://www.accessforums.net/showthread.php?t=75097