Gaps In Time

[Mary]

Hello!
I am curently calculating the differences in,intime and
outime and totaling that amount. What I would also like to
determine is the gap between the last outime and the next
intime.

Lastoutime - nextintime = gaptime (DateDiff)

I would like to take the value in the last outime and
subtract it from the next intime value to determine the
gap between the last outime and the next intime.

What would be the best way to acconplish this task?

Thanks!

 

[Allen Browne]

A subquery is probably the easiest way to get information from another row
of the same table. Results will be read-only.

This example assumes a table named "MyTable", with a foreign key named
"KeyID". It gives you the most recent outtime before this intime for the
same KeyID. Type this expression (one line) into the Field row of query
design:

Lastoutime: (SELECT Max(outime) FROM MyTable AS Dupe
WHERE (Dupe.KeyID = MyTable.KeyID) AND (Dupe.outime < MyTable.intime))