How to do this on Access...

[kiloez]

I have the following in Excel:

Interval Time Diff in Minutes
T1 3/24/2007 2:31:55 PM 0.000000
T2 3/24/2007 2:39:21 PM 7.433333
T3 3/24/2007 3:07:08 PM 27.783333
T4 3/24/2007 3:11:09 PM 4.016667

How can this be replicated in Access? T1 thru Tx and a date time stamp are stored in a table or even different tables. The created query would return the time difference in minutes between intervals. This needs to be done dynamically, without having to add a multitude of related tables to a given query. I am using Access 2003.

 

[ByteMyzer]

Assuming the following structure:

MyTable
-------
Interval
Time

...and the table contains the following data:

Interval | Time
-------------------------------
T1       | 3/24/2007 2:31:55 PM
T2       | 3/24/2007 2:39:21 PM
T3       | 3/24/2007 3:07:08 PM
T4       | 3/24/2007 3:11:09 PM

The following SQL:

SELECT T1.*,
CDbl(Nz((T1.Time-
   (SELECT MAX(T2.Time)
    FROM MyTable AS T2
    WHERE T2.Time<T1.Time
   ))*1440,0)) AS Diff
FROM MyTable AS T1;

...should output these results:

Interval | Time                 | Diff
--------------------------------------------------
T1       | 3/24/2007 2:31:55 PM | 0
T2       | 3/24/2007 2:39:21 PM | 7.43333333171904
T3       | 3/24/2007 3:07:08 PM | 27.783333332045
T4       | 3/24/2007 3:11:09 PM | 4.01666666264646

 

[kiloez]

Thanks a million!!! ByteMyzer rocks!!! Spread the word!!!