How to start a running sum from other calculation

[Peter Bailey]

I have a query that creates a graph of bookings from the course start date
looking back 20 weeks based on a running sum.

I also have a query that counts the number of bookings before that 20 week
date for that particular course start date.

What they want is to start the 20 week running sum from the previous total.

for ex

start date = 1/11/2004
20 week before = 14/6/04
in my case I have sql returning:

Qrygetweeklycountofdosmodulesptb Week Commencing Total Bookings Running
Total Week Number
14/06/2004 0 0 -21
21/06/2004 4 4 -20
28/06/2004 2 6 -19
05/07/2004 3 9 -18
12/07/2004 3 12 -17
19/07/2004 3 15 -16
26/07/2004 3 18 -15
02/08/2004 2 20 -14
09/08/2004 6 26 -13
16/08/2004 6 32 -12
23/08/2004 8 40 -11
30/08/2004 1 41 -10
06/09/2004 2 43 -9
13/09/2004 0 43 -8
20/09/2004 0 43 -7
27/09/2004 0 43 -6
04/10/2004 0 43 -5
11/10/2004 0 43 -4
18/10/2004 0 43 -3
25/10/2004 0 43 -2
01/11/2004 0 43 -1
with total of bookings prior to 14/6/2004 of 26.

What they want is:

Qrygetweeklycountofdosmodulesptb Week Commencing Total Bookings Running
Total Week Number
14/06/2004 0 0 -21
21/06/2004 4 30 -20
28/06/2004 2 32 -19
05/07/2004 3 35 -18
12/07/2004 3 38 -17
19/07/2004 3 41 -16
26/07/2004 3 44 -15
02/08/2004 2 46 -14
09/08/2004 6 52 -13
16/08/2004 6 58 -12
23/08/2004 8 66 -11
30/08/2004 1 67 -10
06/09/2004 2 69 -9
13/09/2004 0 69 -8
20/09/2004 0 69 -7
27/09/2004 0 69 -6
04/10/2004 0 69 -5
11/10/2004 0 69 -4
18/10/2004 0 69 -3
25/10/2004 0 69 -2
01/11/2004 0 69 -1
Sorry for the overkill on info it just avoids mis interpretation.

I am not sure how this can be done so Ideas would be welcome.

Regards in advance
Peter

SQL:

first running sum:

SELECT Pro.[Week Commencing], Pro.[Total Bookings], (Select Sum(Pro1.[Total
Bookings]) from Qrygetweeklycountofdosmoduleenrollments Pro1 where
Pro1.[Week Commencing]<=Pro.[Week Commencing]) AS [Running Total], Pro.[Week
Number]
FROM Qrygetweeklycountofdosmoduleenrollments AS Pro
ORDER BY Pro.[Week Commencing];

Prior bookings:

SELECT Count(Qrygetpriorbookings.Date) AS [Prior Booking Count]
FROM Qrygetpriorbookings;

[Pieter Linden]

there's an article in MSKB that shows how to do a running sum in a query.

[Peter Bailey]

I did that but I phrased the question incorrectly.

I want to start a running sum which I have done but I want to add another
total to the first row and only the first row and then subsequently do the
running sum form there.

I hope i explained that well enough.

regards
Peter
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

there's an article in MSKB that shows how to do a running sum in a query.