Running Total

[David Jones]

I'm trying to create a query that will give me a Running
Total of a field TBTN example below of what it should
look like.
RACEID FIN BTN TBTN
2003100 1 0.00 0.00
2003100 2 0.50 .50
2003100 3 1.75 2.25
2003100 4 0.25 2.50
2003100 5 1.25 3.75
2003101 1 0.00 0.00
2003101 2 5.00 5.00
2003101 3 0.15 5.15
I have tried the following SQL Staement,
SELECT RUNNERS.RACEID, Sum(RUNNERS.BTN) AS SumOfBTN, DSum
("[BTN]","[RUNNERS]","[RACEID]<=" & [RUNNERS].[RACEID])
AS TBTN
FROM RUNNERS
GROUP BY RUNNERS.RACEID;
but it shows.
RACEID BTN TBTN
2003100 3.75 3.75
2003101 5.15 8.90
Any help would be much appreciated.

2002101

 

[Tom Ellison]

Dear David:

SELECT RaceID, FIN,
(SELECT SUM(BTN) FROM Runners T1
WHERE T1.RaceID = T.RaceID
AND T1.FIN <= T.FIN) AS BTN,
(SELECT SUM(TBTN) FROM Runners T1
WHERE T1.RaceID = T.RaceID
ADN T1.FIN <= T.FIN) AS TBTN
FROM Runners T
ORDER BY RaceID, FIN

This is a simple implementation of a "Correlated Subquery" (well, two
implemtations actually) to create a running sum that resets (starts
again from zero) on the first key (RaceID) and runs on a second key
(FIN).

Notice how it performs when there is a tie (2 rows where FIN and
RaceID are the same). It will add in the BTN values of both finishers
simultaneously. Hopefully this is the desired behavior.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[David Jones]

Hi Tom
Thanks very much for your help,you dont know how long its taken me to get this right.
I'm glad to say its working OK now after a minor adjustment .
Once again thanks,
David
----- Tom Ellison wrote: -----

Dear David:

SELECT RaceID, FIN,
(SELECT SUM(BTN) FROM Runners T1
WHERE T1.RaceID = T.RaceID
AND T1.FIN <= T.FIN) AS BTN,
(SELECT SUM(TBTN) FROM Runners T1
WHERE T1.RaceID = T.RaceID
ADN T1.FIN <= T.FIN) AS TBTN
FROM Runners T
ORDER BY RaceID, FIN

This is a simple implementation of a "Correlated Subquery" (well, two
implemtations actually) to create a running sum that resets (starts
again from zero) on the first key (RaceID) and runs on a second key
(FIN).

Notice how it performs when there is a tie (2 rows where FIN and
RaceID are the same). It will add in the BTN values of both finishers
simultaneously. Hopefully this is the desired behavior.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I'm trying to create a query that will give me a Running
Total of a field TBTN example below of what it should
look like.
RACEID FIN BTN TBTN
2003100 1 0.00 0.00
2003100 2 0.50 .50
2003100 3 1.75 2.25
2003100 4 0.25 2.50
2003100 5 1.25 3.75
2003101 1 0.00 0.00
2003101 2 5.00 5.00
2003101 3 0.15 5.15
I have tried the following SQL Staement,
SELECT RUNNERS.RACEID, Sum(RUNNERS.BTN) AS SumOfBTN, DSum
("[BTN]","[RUNNERS]","[RACEID]<=" & [RUNNERS].[RACEID])
AS TBTN
FROM RUNNERS
GROUP BY RUNNERS.RACEID;
but it shows.
RACEID BTN TBTN
2003100 3.75 3.75
2003101 5.15 8.90
Any help would be much appreciated.

2002101