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(DatumTimeSt amp)
FROM [Data]
WHERE DatumTimeStamp < t.DatumTimeStam p),
DatumTimeStamp) As SecondsTimeLaps e

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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQjONx4echKq OuFEgEQKMBQCg9o uDLbjDIhcoH2CAq wAS6EVYfgoAoL7U
QkLQpxT3afDjQ44 W/S/vxaKn
=RGfs
-----END PGP SIGNATURE-----

[MLH]

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

Perhaps:

SELECT DatumTimeStamp,
DateDiff("s", (SELECT Max(DatumTimeSt amp)
FROM [Data]
WHERE DatumTimeStamp < t.DatumTimeStam p),
DatumTimeStamp) As SecondsTimeLaps e

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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Russell]

MLH <CR**@NorthStat e.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.DatumTimeStam p ORDER BY DatumTimeStamp ASC),
D.DatumTimeStam p)
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 [SecondsTimeLaps e] 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.DatumTimeStam p AS Expr1, IIf(DateDiff("s ",(SELECT
Max(DatumTimeSt amp)
FROM [tblDataTESTING]
WHERE DatumTimeStamp <
t.DatumTimeStam p),[DatumTimeStamp])>60,60,DateDif f("s",(SELECT
Max(DatumTimeSt amp)
FROM [tblDataTESTING]
WHERE DatumTimeStamp <
t.DatumTimeStam p),[DatumTimeStamp])) AS SecondsTimeLaps e
FROM tblDataTESTING AS t;

[MLH]

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

SELECT t.DatumTimeStam p AS Expr1, IIf(DateDiff("s ",(SELECT
Max(DatumTimeSt amp)
FROM [tblDataTESTING]
WHERE DatumTimeStamp <
t.DatumTimeStam p),[DatumTimeStamp])>60 or IsNull(DateDiff ("s",(SELECT
Max(DatumTimeSt amp)
FROM [tblDataTESTING]
WHERE DatumTimeStamp <
t.DatumTimeStam p),[DatumTimeStamp])),60,DateDiff( "s",(SELECT
Max(DatumTimeSt amp)
FROM [tblDataTESTING]
WHERE DatumTimeStamp <
t.DatumTimeStam p),[DatumTimeStamp])) AS SecondsTimeLaps e
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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)