calculate values between rows

[Guest]

I've a column with values and I want another column with the calculation of
the difference between the current and the previous column, something like
this:
col 1 variation (from rows of column 1)
10 10
40 30
60 20

Is this possible with a simple expression? Tks in advance

 

[Allen Browne]

Use a subquery to get the value from the previous row. Once you have that
value you can extract it from the current row in order to get the
difference.

The question arises as to how you define "previous". The example below
assumes there is an AutoNumber field named ID as well, so you can sort by
this number to define the order of the records:

SELECT [Col 1],
[Col 1] - (SELECT TOP 1 Dupe.[Col 1]
FROM Table1 AS Dupe
WHERE Dupe.ID > Table1.ID
ORDER BY Dupe.ID) AS Variation
FROM Table1
ORDER BY ID;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Guest]

But I can't do this with an Expression? I hope I can use something like this
in the fiel variation:
=([row.col1]-[row+1.col1)/[row.col1]

How can I select a row?

Use a subquery to get the value from the previous row. Once you have that
value you can extract it from the current row in order to get the
difference.

The question arises as to how you define "previous". The example below
assumes there is an AutoNumber field named ID as well, so you can sort by
this number to define the order of the records:

SELECT [Col 1],
[Col 1] - (SELECT TOP 1 Dupe.[Col 1]
FROM Table1 AS Dupe
WHERE Dupe.ID > Table1.ID
ORDER BY Dupe.ID) AS Variation
FROM Table1
ORDER BY ID;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I've a column with values and I want another column with the calculation
of
the difference between the current and the previous column, something like
this:
col 1 variation (from rows of column 1)
10 10
40 30
60 20

Is this possible with a simple expression? Tks in advance

 

[John Vinson]

But I can't do this with an Expression? I hope I can use something like this
in the fiel variation:
=([row.col1]-[row+1.col1)/[row.col1]

If Access were a spreadsheet with rows and columns, you could. But it
ISN'T.

A spreadsheet program like Excel handles spreadsheet logic; a
relational database like Access uses different logic. Though a table
looks rather like a spreadsheet, it *isn't* - for one thing, there are
no row numbers or column letters. A table is an unordered "heap" of
data; in order to see information from another record, you need to use
a Subquery as Allen suggests.

John W. Vinson[MVP]