Trying to figure out how to create a cumulative time field

[Guest]

Hi all,

I'm trying to figure out how to create a report that will provide a
cumulative time result for the purposes of tracking the progress of runners
in a team race. In this case I want to present the report such that the Leg
of the Race is the First Grouping and then to present the Results by Year.
For example Leg 1 and then the cumulative time (for Years 1994 through 2000)
and then Leg 2 (for Years 1994 through 2000) and then onto Leg 3 and so on.

The cumulative time will build from Group to Group for Legs 1 through 20.

The example is as follows (NOTE that I have removed the fields which include
the runner names, pace, and distance for simplifying presentation here):

Leg Year Time Cum. Start Time End Time
1
1994 0:46:35 0:46:35 12:00:00 PM 12:46:00 PM
1995 0:47:15 0:47:15 2:00:00 PM 2:47:00 PM
1996 0:59:22 0:59:22 3:00:00 PM 3:59:00 PM
1997 1:00:34 1:00:34 12:00:00 PM 1:00:00 PM
1998 0:58:23 0:58:23 12:00:00 PM 12:58:00 PM
1999 0:43:52 0:43:52 12:00:00 PM 12:43:00 PM
2000 0:47:57 0:47:57 9:30:00 PM 10:17:00 PM
2
1994 1:05:30 1:05:30 12:00:00 PM 1:05:00 PM
1995 0:53:21 0:53:21 2:00:00 PM 2:53:00 PM
1996 0:56:00 0:56:00 3:00:00 PM 3:56:00 PM
1997 0:54:36 0:54:36 12:00:00 PM 12:54:00 PM
1998 0:49:03 0:49:03 12:00:00 PM 12:49:00 PM
1999 0:52:10 0:52:10 12:00:00 PM 12:52:00 PM
2000 0:54:04 0:54:04 9:30:00 PM 10:24:00 PM

Note: that the cumulative time for Leg 2, Year 1994 will be cumulative
(includes the Year1 Cum. Time plus Year2 Cum. Time and that the Leg Start and
End Times should behave in a similar manner and allow for switching from PM
to AM as the time progresses.

Thanks in advance...

 

[Allen Browne]

See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html
The article explains how to use DateDiff() in a query to get the number of
minutes between the Start Time and End Time.

You can then set the Running Sum property of the text box on the report so
that it accumulates the time over the report's records.

 

[Guest]

Allen,

Thanks for your response. I understand how your solution works, but what if
we have a table that only has the "StartDateTime" field and also a field that
records the elapsed time for the runner on Leg 1 and no "EndDateTime" field?

I have already created a report that will correctly calculate the cumulative
when I am looking at it from the perspective of Year 1 of race which presents
Legs 1 through Leg 20 in the first Group where I only provide the Start Time
of the race and the elapsed time for each Leg of the race and Access provides
the end time of each Leg and the cumulative time at the end of each Leg.

However, in my second scenario presented below, I also want to look at the
race starting with the Legs as the Groups, not the Years. In this case I
want Access to calculate the total elapsed time as it changes from Leg to
Leg, beginning with a fixed Start Time at Leg 1 and a known elapsed time for
each Leg, where Access provides the equivalent of the "EndDateTime" field?

In other words, I want to present the report as Leg 1 and then the results
for each year (years 1 through 14) such that I can compare the runners'
performances for any one Leg from Year to Year and also to keep a cumulative
time from Year to Year as you review each Leg as presented in my example
below. The total elapsed times and leg start and end times should flow from
Leg to Leg for each year.

Thanks again.

 

[Allen Browne]

The details of the answer will depend on how you have the data stored.

You have races in different years, and in each year there are some 20 legs.
The normalized table would be:
TheYear (4-digit year)
TheLeg (a number between 1 and 20)
Seconds (number of seconds for that leg in that year.)

With that structure, you can group by TheLeg, and sum the durations.

If you want to present a matrix where there is a column for each year and a
row for each leg, that would be a crosstab query.

Since you are storing a duration in seconds, you can calculate the end
date/time if you wish:
DateAdd("s", [Seconds], [StartDateTime])

Looking back at your original post here, you could do this very easily in a
report. In the Sorting And Grouping dialog (View menu in report design),
enter 2 rows for:
Leg (with a group header)
Year (sort ascending)
Now put Seconds in the Detail Section, and set its Running Sum property to:
Over Group
so it accumulates the total number of seconds for the leg.