Running total for elapsed time?

  • Thread starter Thread starter Fuzzy Logic
  • Start date Start date
F

Fuzzy Logic

I have a table with the relevant fields being Number, StartTime, EndTime. I
wish to produce a query that will display the Number, difference in time and
a running total for the difference in time per number. For example:

1 0:30 0:30
1 0:15 0:45
1 1:00 1:45
2 0:15 0:15
2 0:30 0:45
2 1:15 2:00

I've had no problem calculating the time difference based on start and end
time but cannot figure out how to get the running total. Any help would be
greatly appreciated.
 
Since the order of records is important here, you will need some field (such
as an autonumber) that defines the order of records.

Create a query into this table. Type a subquery into a fresh column in the
Field row. Something like this:
Minutes: (SELECT Sum(DateDiff("n", Dupe.StartTime, Dupe.EndTime))
FROM Table1 AS Dupe
WHERE (Dupe.ForeignID = Table1.ForeignID)
(AND Dupe.ID <= Table1.ID))

Assumes:
- table named Table1;
- foreign key named ForeignID (the 1, and 2 in your example below);
- primary key named ID (the new autonumber);
- a read-only result is okay;
- display in minutes is okay.
 
Back
Top