Calculated Field

[carl]

My data is like this:

NewTime ID
9:31:16 1
9:31:37 2
9:31:41 3
9:31:50 4
9:31:53 5
9:32:02 6

Is there a way to subtract ID 2 from 1, 3 from 2, etc ?

Thank you in advance.

 

[MGFoster]

My data is like this:

NewTime ID
9:31:16 1
9:31:37 2
9:31:41 3
9:31:50 4
9:31:53 5
9:32:02 6

Is there a way to subtract ID 2 from 1, 3 from 2, etc ?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't you really mean substract the times?

SELECT T1.ID, T1.NewTime,
T1.NewTime - (SELECT Max(NewTime) FROM table_name As T3
WHERE T3.ID = T1.ID-1 AND T3.NewTime < T1.NewTime)
As Duration

FROM table_name As T1

ORDER BY T1.NewTime

Substitute the real table name for "table_name."

If the ID has any gaps you might get some errors, or NULLs. To fix that
you'd have to put another subquery in the 1st subquery:

WHERE T3.ID = (SELECT MAX(T4.ID) FROM table_name AS T4 WHERE T4.ID <
T1.ID) AND T3.NewTime < T1.NewTime) ... etc.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRGEf8YechKqOuFEgEQLOpwCff2PKhGirCH3mKasRnr3689i8OcEAn1S2
k02McYigxXGf417Oz2oO6nr1
=b20w
-----END PGP SIGNATURE-----