By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

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

P: n/a
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?
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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.
Nov 13 '05 #2

P: n/a
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-----
Nov 13 '05 #3

P: n/a
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.


Nov 13 '05 #4

P: n/a
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)
Nov 13 '05 #5

P: n/a
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
Nov 13 '05 #6

P: n/a
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?
Nov 13 '05 #7

P: n/a
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;

Nov 13 '05 #8

P: n/a
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.
Nov 13 '05 #9

P: n/a
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)
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.