Table contains date/time field, I want to query it displaying time-diff between successive records entered. How?

[MLH]

I have a database (datatrek.mdb) with a table named DATA.
The table has a date/time field [DatumTimeStamp] with default
value = Now(). It has 100 records in it entered over a 50-minute
period.

I would like the query to display 100 records with a new, calculated
field showing timelapse between time of record entry of current record
and time of entry of previous record. For the first record in the
dynaset, I'll settle for a value = 30 seconds. For the last 99 records
in the dynaset, I want to see the ACTUAL time-diffs between the
current and prior record.

Can I make a query do that somehow?

[MLH]

For what its worth, I am hoping that a query can be created
to do what I've described in order to avoid an additional data
field in the table to house the absolute timelapse data.

[MGFoster]

MLH wrote:

I have a database (datatrek.mdb) with a table named DATA.
The table has a date/time field [DatumTimeStamp] with default
value = Now(). It has 100 records in it entered over a 50-minute
period.

I would like the query to display 100 records with a new, calculated
field showing timelapse between time of record entry of current record
and time of entry of previous record. For the first record in the
dynaset, I'll settle for a value = 30 seconds. For the last 99 records
in the dynaset, I want to see the ACTUAL time-diffs between the
current and prior record.

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

Perhaps:

SELECT DatumTimeStamp,
DateDiff("s", (SELECT Max(DatumTimeStamp)
FROM [Data]
WHERE DatumTimeStamp < t.DatumTimeStamp),
DatumTimeStamp) As SecondsTimeLapse

FROM [Data] As t

You could also use the DMax() function instead of the subquery.

See the Access Help articles on DateDiff() and DMax() for more info.

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

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

iQA/AwUBQjONx4echKqOuFEgEQKMBQCg9ouDLbjDIhcoH2CAqwAS6E VYfgoAoL7U
QkLQpxT3afDjQ44W/S/vxaKn
=RGfs
-----END PGP SIGNATURE-----

[MLH]

Hey! That worked! How in the heck did you do that, man?
You're a genius!
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Perhaps:

SELECT DatumTimeStamp,
DateDiff("s", (SELECT Max(DatumTimeStamp)
FROM [Data]
WHERE DatumTimeStamp < t.DatumTimeStamp),
DatumTimeStamp) As SecondsTimeLapse

FROM [Data] As t

You could also use the DMax() function instead of the subquery.

See the Access Help articles on DateDiff() and DMax() for more info.

[MGFoster]

MLH wrote:

Hey! That worked! How in the heck did you do that, man?
You're a genius!

I'd like to take credit for it, but I devised it based on info in a
Query Examples db provided by Microsoft.

http://www.microsoft.com/downloads/d...displaylang=en

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

[Russell]

MLH <CR**@NorthState.net> wrote in message news:<ak********************************@4ax.com>. ..

I have a database (datatrek.mdb) with a table named DATA.
The table has a date/time field [DatumTimeStamp] with default
value = Now(). It has 100 records in it entered over a 50-minute
period.

I would like the query to display 100 records with a new, calculated
field showing timelapse between time of record entry of current record
and time of entry of previous record. For the first record in the
dynaset, I'll settle for a value = 30 seconds. For the last 99 records
in the dynaset, I want to see the ACTUAL time-diffs between the
current and prior record.

Can I make a query do that somehow?

This can be done. Depends on what you consider the "previous" record.
You can use a subquery such as the following if you're comparing based
on date.

SELECT *, DateDiff("s", (SELECT TOP 1 DatumTimeStamp FROM DATA WHERE
DatumTimeStamp < D.DatumTimeStamp ORDER BY DatumTimeStamp ASC),
D.DatumTimeStamp)
FROM DATA AS D

If you want to compare based on ID values, just change the inner WHERE
clause to compare the IDs using the same idea.\

HTH,
Russell Sinclair

[MLH]

Could the SQL be taken one step further to never show more than 60
in the [SecondsTimeLapse] field of the query? In other words, any
value greater than 60 calculated by the DateDiff expression would be
shown simply as 60. Wanna take a stab at that?

[MLH]

Well, I tried this. It worked. I'm happy.

SELECT t.DatumTimeStamp AS Expr1, IIf(DateDiff("s",(SELECT
Max(DatumTimeStamp)
FROM [tblDataTESTING]
WHERE DatumTimeStamp <
t.DatumTimeStamp),[DatumTimeStamp])>60,60,DateDiff("s",(SELECT
Max(DatumTimeStamp)
FROM [tblDataTESTING]
WHERE DatumTimeStamp <
t.DatumTimeStamp),[DatumTimeStamp])) AS SecondsTimeLapse
FROM tblDataTESTING AS t;

[MLH]

In the final analysis, this gave me PRECISELY what I needed...

SELECT t.DatumTimeStamp AS Expr1, IIf(DateDiff("s",(SELECT
Max(DatumTimeStamp)
FROM [tblDataTESTING]
WHERE DatumTimeStamp <
t.DatumTimeStamp),[DatumTimeStamp])>60 or IsNull(DateDiff("s",(SELECT
Max(DatumTimeStamp)
FROM [tblDataTESTING]
WHERE DatumTimeStamp <
t.DatumTimeStamp),[DatumTimeStamp])),60,DateDiff("s",(SELECT
Max(DatumTimeStamp)
FROM [tblDataTESTING]
WHERE DatumTimeStamp <
t.DatumTimeStamp),[DatumTimeStamp])) AS SecondsTimeLapse
FROM tblDataTESTING AS t;

Thx again, Mr Foster.

[MGFoster]

MLH wrote:

In the final analysis, this gave me PRECISELY what I needed... < snip > Thx again, Mr Foster.

You're welcome.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)