• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 63
  • Last Modified:

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.
0
Daniel Serbanescu
Asked:
Daniel Serbanescu
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
Daniel SerbanescuAuthor Commented:
thank you very much. it is fine as it it.
0
 
Daniel SerbanescuAuthor Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now