Subtracting within a field

[steenman2]

I am looking to subtract number inside the same field. and then return
the difference to a new field (column). This data is used to generate a
graph and the this is currently done by hand. I know there has to be an
easier way to do this.

Below is an example of what I am looking for.


Value(s) Result(s)
-70 0
-70 1.02
-68.98 1.09
-67.89 3.11
-71 .71
-69.29 1.52
-67.77

So what I am doing is subtracting row two from row one. The difference
is then put in another field(column) in row one. The process repeats,
row three from two, four from three, five from four, etc, etc. This
occurs for hundreds of rows.

Is there a SQL statement that can do this or is it something that has
to be done in VB.

Thanks for your help.

 

[JethroUK©]

you can - but you need to define how you order the records (fundimatenl to
the calculation) - this maybe in order of creation (primary key) - but could
be in some other order

 

[David Austria]

Hi.

I add a column name "Orden" in the table "Tabla1". The column "Orden"
The column "Order" is a consecutive number without spaces.

Orden Value
1 -70
2 -70
3 -68.98
4 -67.89
5 -71
6 -69.29
7 -67.77

The SQL statement is:

SELECT Tabla1.Value, Tabla1.Value - (SELECT Top 1 B.Value FROM Tabla1
AS B WHERE B.Orden = Tabla1.Orden+1) AS Result FROM Tabla1 ORDER BY
Tabla1.Orden;

Value Result
-70 0
-70 -1.02
-68.98 -1.09
-67.89 3.11
-71 -1.70999999999999
-69.29 -1.52000000000001
-67.77

Greetings
David Austria

 

[steenman2]

I am not sure I understand what B is in your statement. Ex. B.Value and
or B.Orden.
Right now both the Value and Result fields are in the same table. (
wasn't sure if that is what B was).

Any more hints,
Thanks again.

 

[steenman2]

Nevermind, it works.

Thanks guys