Calculating a field where its value depends on the same field of the previous row

[toni2oni]

I'm trying to get some help with how to write a query. I have a table that contains the Year and a value EC. Using that table, I am trying to produce the table below.

Year	BBF	EC	Int	Total
2013	-	25	1.00	26.00
2014	26.00	85.96	3.44	115.40
2015	115.40	100.05	4.00	219.45
2016	219.45	205.96	8.24	433.65
	Total from previous year		4%(BBF+EC)	BBF+EC+Int

I'm not sure how to write the sql query as the total is dependent on the BBF, but the BBF fields also needs the total from the previous year.

Any help would be appreciated.

Thanks

[June7]

Review http://allenbrowne.com/subquery-01.html#AnotherRecord

Or build a report and use textbox RunningSum property.

[toni2oni]

In the example on the link, the subquery selects a value that already exists in the table. In my scenario, the previous value that I need [Total] doesn't exist in the table, it is calculated using BBF which itself depends of the total from the previous row.

[June7]

You're right, after taking a closer look, a query is not practical.

I tested a report use RunningSum property and it produces the exact output as your example.

[toni2oni]

How to use RunningSum in the report?

[June7]

Name texboxes like tbxEC, tbxInt and bind them to respective fields. Another textbox named tbxTotal with expression in ControlSource: =[EC]+[Int]. Set RunningSum property to OverAll.

Another textbox named tbxBBF with expression in ControlSource: =tbxTotal-tbxEC-tbxInt

[toni2oni]

The thing is, INT is also a calculated field based on the BBF field.
Int = 4%(BBF+EC).

[June7]

Really!! Are you trying to drive me insane? So Int is not data in table? Okay, I should have paid more attention to formulas in the last row of your table.

Some new textbox names.

OverAll RunningSum in tbxSumEC: =[EC]

OverAll RunningSum in tbxSumInt: =[tbxEC]*0.04

Not RunningSum in tbxTotal: =[tbxSumEC]+[tbxSumInt]

Not RunningSum in tbxInt: =[tbxEC]*0.04

Not RunningSum in tbxBBF: =[tbxTotal]-[tbxEC]-[tbxInt]

You can even hide the running sum textboxes.

[ssanfu]

FYI........

"INT" is an extremely poor choice for an object name. It is a (JET) reserved word AND a built in function.




[BeginRant]

You are using Calculated fields in tables?? Tsk, tsk. The calculation should be in query.

[/EndRant]

[June7]

I think by 'produce the table' OP meant a query datasheet. Query is mentioned in first and last sentences of OP

And Year is also a reserved word - it is a function.

[toni2oni]

I think by 'produce the table' OP meant a query datasheet. Query is mentioned in first and last sentences of OP

And Year is also a reserved word - it is a function.

Correct!

Thanks for your help for far June7. I'm still not getting the figures that I want. I realized that I, in fact, made an error in the table above but the formula in the last row is what it should be. The table values should look like this.

Year	BBF	EC	Int	Total
2013	-	25	1.00	26.00
2014	26.00	85.96	4.48	116.44
2015	116.44	100.05	8.66	225.15
2016	225.15	205.96	17.24	448.85
	Total from previous year		4%(BBF+EC)	BBF+EC+Int

I might be trying to do the impossible.

OverAll RunningSum in tbxSumInt should really be: =([tbxEC]+[tbxBBF])*0.04 but this throws an error

If there's no other way I'll simply export the data to excel and format afterwards.

Thanks

[ArviLaanemets]

It is not impossible in MS Access, but it is impossible for MS Access query. To get the wanted result you need to write a recursive query, which isn't supported in Access.

To do this in Access, you have to write a procedure, which:
1. Creates an empty recordset with structure you need to be returned;
2. Determines the 1st and last year in your table and saves values to variables;
3. Calculates values for 1st year (you can define a querystring in VBA and run it) and writes the result as row into recordset;
4. Saves Total into variable;
5. The Start of cycle! Saves next year (1st year + 1) into variable;
6. If saved year is bigger than last year, then ends the procedure, otherwise continues;
7. Tests for existence of next year. If such exists, then continues, otherwise starts new cycle (I hope this will not happen, otherwise you have to decide how proceed with your calculations!);
8. Calculates values for active year (you can use the same querystring as at start, but you assign saved Total value as BBF) and writes the result as row into recordset;
9. Saves new Total into variable and returns to 5. The end of cycle!;
10. Returns the recordset.

[June7]

Yep, officially insane now. Options appear to be Arvil's suggestion or export to Excel.

[ArviLaanemets]

Options appear to be Arvil's suggestion or export to Excel.

Or to port DB to MS SQL Server, where it is possible to create a recursive query. But however you describe a recursive query, you don't use a word "simple" there