Query Calculation challenge

[Perfac]

I know how to do a DSum that do a running sum in a query but that will not solve my challenge. This makes part of our wanted Budget process. As example if a business starts on 1 Jan 2016 and financial year ends are end of Feb. Three tables are used for the Budget process. t09InflationRates, t09Budget and t09Budgetamounts. This detail doesnt matter too much here. The attachment is a Excell spreadsheet and display we would like Access to do the same. If on a Budget item an expense of 500 is started of with, we know how to return the budgeted inflation. As you may see on the excell. It must then be taken into account that on the 1 Nov 2016 a decision was made that the budget will jump to 4000. Whatever the budget amount is in Feb 2017, the inflation rate estimated of 8% must be added to get to March 2017. We are still fighting to get this.

[kd2017]

I don't understand what you are asking. Can you elaborate further? Can you show the equations used in the spreadsheet? Can you show your current access table setup? "Monthly budget" and "inflation" are user input, and "new higher budget" is output, right?

[Perfac]

I thought it will help to add images of the queries where data is derived from.

[Perfac]

q09BudgetAmounts starts of with 50 records, it means 50 forms are entered, to start. Only when a deviation is needed maybe even a couple of years later another form will be processed. This process will see a budget in principle available for any amount of years in future without any more work, except if a deviation is needed. We have 50 Income and Expense accounts on which we Budget. 8 Income and 42 Expense. If we budget for 5 years or 60 months, there will be 3000 records in query q09Budget. I assume this calculation can be done, we must find the way.

[kd2017]

Sorry the SQL is a little over my head right now. However, for clarity's sake I remade your spreadsheet. Hopefully it'll help another member better understand what you are trying to do.



EDIT:
A better formula might be =IF( ISBLANK( [Budget Input] ), [Prev Calc Monthly Budget], [Budget Input] ) * (1 + [Inflation Input] )

[Perfac]

Thanks KD. I will try.
Our process on your spreadsheet.
1. On any one Budget item let's say Commission Paid the starting budget is 500 p.m.
2. A record is registered as you can see in q09BudgetAmount. The dates will see q09Budget getting the 500 from q09BudgetAmount.
3. Every March is the first of financial years and inflation is added with the intention that he now increased 525 will be fixed for the next 12 months.
4. As naturally may happen the business decided on 1Nov 2016 there will be a substantial increase to R4000 p.m.
5. From here the calculation forget of any previous numbers. The budget continue on the 4000 for as many years there is no new form entered in q09BudgetAmount.
6. In this example there is a new start of 7000 on the 01July2017. So the next estimated inflation of 9% is added to the 7000.
7. I didn't want to give too much information but our form f09Budget have a date that filters q09Budget to budget only up to a selected month or financial year end. If this is open the calculations will budget for as many months is stored in the t01Months table.

Excel can easily calculate the amount of the previous line. In access I am not fresh enough today to work it out, but I have no doubt there is a way. If you understand me correct all this talking can be easy if I say. I have the solution if I know in Access an extra field in the query that should look like the spread sheet you posted. This extra field, what expression cab do the following.
a. Line with date 7/1/2017. Iif BudgetInput field > 0 then BudgetInput, otherwise BudgetAmount 4320 which falls in the previous line.
b. If the business decided on a new budget amount atrting March, that new amount only should be in the budget field, not vat added.
c. If line with date 3/1/2018 the calculation may return Iif Inflation > 0 then previous record amount plus Inflation otherwise just the previous record amount.

You cant see it in my attachments there is a field MonthNumberinBusiness in all queries which may help to bring it together.

[kd2017]

[edit] nvm