asked on

add a value to the previous value in access query

I have a query for which I would like to add the following column (they are all Doubles):

column1 column2 - this it the column I want to add

value1 x1 = value1 from column1, the same number
value2 x2 = x1 + 0.1*x1 + value2
value3 x3 = x2 + 0.1*x2 + value3
value4 x4 = x3 + 0.1*x3 + value4
value5 x5 = x4 + 0.1*x4 + value5
and so on..

I suppose I either need a function or use some subqueries. Thank you.

Pawan Kumar

Please try this ..

There is nothing built in access for this. Either you can write a SQL query or you can also use DSUM.

You can replace Id column with any sequence column you have. You have to run the query in the SQL query window ( MS ACCESS SQL View -> http://www.jaffainc.com/SQLStatementsInAccess.htm )

You also need to replace your column names, table names with your actual names.

SELECT id, value1 ,
  NZ( value1 + 0.1*value1 +
    (
	SELECT Sum(Value1) AS Total 
  FROM Doubles AS T2
  WHERE T2.id < T1.id),value1) AS k
FROM Doubles AS T1

/*------------------------
OUTPUT
------------------------*/
id          value1                 k
----------- ---------------------- ----------------------
1           10                     10
2           12                     23.2
3           3                      25.3
4           15                     41.5
5           23                     65.3

Open in new window

Daniel Serbanescu

ASKER

Thanks, Kumar, it doesn't really do what I want at this point. Maybe you could tune it a little bit.

With your numbers, I want it like this:

value1 k

10 10
12 10+0.1*10+12=23, the second value in this column should be 23
3 23+0.1*23+3=28.3, the third value in this column should be 28.3
15 28.3+0.1*28.3=46.13, the fourth value in this column should be 46.13
23 here should be 73.743 = 46.13+46.13*0.1+23

Daniel Serbanescu

ASKER

sorry, the fourth row should be 15 then 28.3+0.1*28.3+15=46.13

als315

Daniel, Access can't work like Excel. It doesn't know anything about sequence of values without additional fields. It can be primary key, date etc. With additional field you can, for example, in query sum values (and make other calculations) with date less then current. May be you can upload sample DB with this table?

Daniel Serbanescu

ASKER

Ok, here is the database. I want to add another column in the query which performs the calculations using data from this new column and adding the q6 column as described above.
test.accdb

Daniel Serbanescu

ASKER

I don't mind using more queries or more columns, I just want the calculation to be done automatically.

als315

Do you like to do it separately for each payer?

Daniel Serbanescu

ASKER

no, just the final value from "q6" is fine, thank you. I need one value per day.

als315

Can you prepare sample in Excel of expected result?

Daniel Serbanescu

ASKER