Summing distance data between 2 dates that vary

J

JB

I have a table that contains the dates, distances and times of all my
training runs and races. (An extract is shown below). I want to run a query
to calculate the aggregate distance run in periods of 1 week, 1 month and 3
months before each race date. Any thoughts on the best way of tackling this?

Date Event Distance
28-Jul-09 Bridge Inn 5k 5.00
26-Jul-09 Conkwell Killer 13.40
23-Jul-09 Training 9.18
21-Jul-09 Coaching 8.37
07-Jul-09 Coaching 7.00
 
J

John Spencer

You will need three sub-queries to get the solution. You may need to redefine
the ranges depending on hojw you are defining the period before each race date

SELECT [Date], Event
, (SELECT Sum(Distance) FROM
as T2
WHERE T2.[Date] >= T1.[Date]-6 and
T2.[Date] < T1.[Date]) as WeekTotal
, (SELECT Sum(Distance) FROM
as T2
WHERE T2.[Date] >= DateAdd("m",-1,T1.[Date]) and
T2.[Date] < T1.[Date]) as Month_1Total
, (SELECT Sum(Distance) FROM
as T2
WHERE T2.[Date] >= DateAdd("m",-3,T1.[Date]) and
T2.[Date] < T1.[Date]) as Month_3Total
FROM
as T1

If there are more than one event per day and you want the numbers to include
the other races on that date, then you will need a way to distinguish the
order of the events on the date (storing the date and time in the same field
would work for that).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Top