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.
Daniel SerbanescuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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

0
Daniel SerbanescuAuthor Commented:
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
0
Daniel SerbanescuAuthor Commented:
sorry, the fourth row should be 15      then 28.3+0.1*28.3+15=46.13
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

als315Commented:
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?
0
Daniel SerbanescuAuthor Commented:
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
0
Daniel SerbanescuAuthor Commented:
I don't mind using more queries or more columns, I just want the calculation to be done automatically.
0
als315Commented:
Do you like to do it separately for each payer?
0
Daniel SerbanescuAuthor Commented:
no, just the final value from "q6" is fine, thank you. I need one value per day.
0
als315Commented:
Can you prepare sample in Excel of expected result?
0
Daniel SerbanescuAuthor Commented:
here is an excel which has the query from the database and the column that I want in access
0
Daniel SerbanescuAuthor Commented:
0
aikimarkCommented:
x1 + 0.1*x1 should be simplified to 1.1*x1
0
Daniel SerbanescuAuthor Commented:
sure. that's fine. (the actual figure is 0.001 so it will be 1.001 because I need to ad 0.1%)
0
als315Commented:
Access is not the spreadsheet, so such calculations are not simple. I'm not sure it could be done in query, but even it is possible, it will work very slowly. For such cases usually used temporary tables, which are filled with code. Look at sample (press button on a form)
test.accdb
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniel SerbanescuAuthor Commented:
thank you very much. it is fine as it it.
0
Daniel SerbanescuAuthor Commented:
thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.