Subtracting consecutive Rows...

[FederalProgrammer]

Any ideas?
is there a way to subtract consecutive rows of a table using SQL on Access???
ie, newField.row(i) = row(i) - row(i-1)

Obviously I need to get two copies of the table... and some how use the IIF function (maybe!!) i don't know...
any help is much apreciated!!

 

[Lightning]

You could use the DLookUP() function to select the value from the previous row and then perform your calculation. This would not require two copies of the table, but would require some consecutively numbered or indexed field to be able to select the previous row.

DLookup Function
You can use the DLookup function to get the value of a particular field from a specified set of records (a domain). Use the DLookup function in Visual Basic, a macro, a query expression, or a calculated control on a form or report.

You can use the DLookup function to display the value of a field that isn't in the record source for your form or report. For example, suppose you have a form based on an Order Details table. The form displays the OrderID, ProductID, UnitPrice, Quantity, and Discount fields. However, the ProductName field is in another table, the Products table. You could use the DLookup function in a calculated control to display the ProductName on the same form.

DLookup(expr, domain, [criteria])
The DLookup function has the following arguments.

Argument Description
expr An expression that identifies the field whose value you want to return. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field . In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
domain A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.
criteria An optional string expression used to restrict the range of data on which the DLookup function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DLookup function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null.


Remarks
The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain.

If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null.

If more than one field meets criteria, the DLookup function returns the first occurrence. You should specify criteria that will ensure that the field value returned by the DLookup function is unique. You may want to use a primary key value for your criteria, such as [EmployeeID] in the following example, to ensure that the DLookup function returns a unique value:

Dim varX As Variant
varX = DLookup("[LastName]", "Employees", "[EmployeeID] = 1")

Another option might be to open two recordsets of the table and loop through to the required records and then perform your calculation.

A third possible option is to open a recordsetclone and use bookmarks to locate each record and then perform your calculation.

I haven't tested either of these two options, but each should work. But the Dlookup would be the fastest way to get the values.

HTH
Lightning

 

[cmmrfrds]

In the simple example you show a self join would work as long as the table has a unique field like an autonumber.

select A.id,
(A.Field - B.Field) as diff
from tab as A, tab as B
where A.id < B.id

 

[FederalProgrammer]

Actually I found this awesome two liner which is super simple than any thing else:

Select M2.Value-M1.Value from MyTable M1 left join MyTable M2
on M1.TimeStep=M2.TimeStep+1 order by M1.TimeStep

thanx for your helps though!