By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

running total in query

P: n/a
I have a query that returns weekly enrollments:

Qrygetweeklycountofdosmoduleenrollments Week Commencing Week Ending Total
Bookings
02/04/2004 02/04/2004 0
05/04/2004 09/04/2004 0
12/04/2004 16/04/2004 1
19/04/2004 23/04/2004 2
26/04/2004 30/04/2004 1
03/05/2004 07/05/2004 1
10/05/2004 14/05/2004 1
17/05/2004 21/05/2004 0
24/05/2004 28/05/2004 2
31/05/2004 04/06/2004 1
07/06/2004 11/06/2004 1
14/06/2004 18/06/2004 0
21/06/2004 25/06/2004 3
28/06/2004 02/07/2004 3
05/07/2004 09/07/2004 4
12/07/2004 16/07/2004 2
19/07/2004 23/07/2004 3
26/07/2004 30/07/2004 6
02/08/2004 06/08/2004 4
09/08/2004 13/08/2004 2
16/08/2004 20/08/2004 15
I would like to get a running sum of the total bookings column I am not sure
how to do this.

Many regards in advance
Peter
SQL at present
:
SELECT Min(Qrygetdailycountofdosmoduleenrollments.Date) AS [Week
Commencing], Max(Qrygetdailycountofdosmoduleenrollments.Date) AS [Week
Ending], Sum(Qrygetdailycountofdosmoduleenrollments.[Number of Module
Enrollments]) AS [Total Bookings]
FROM Qrygetdailycountofdosmoduleenrollments
GROUP BY Qrygetdailycountofdosmoduleenrollments.[Week Number];


Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
create a report and create a running sum of that field.
Nov 13 '05 #2

P: n/a
I wanted to create the running sum in the query itself and not in a report.
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
create a report and create a running sum of that field.

Nov 13 '05 #3

P: n/a
Does your data contain a unique ID column?

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:cg**********@news8.svr.pol.co.uk...
I wanted to create the running sum in the query itself and not in a report.

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
create a report and create a running sum of that field.


Nov 13 '05 #4

P: n/a


yes the week commencing date is the unique column

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #5

P: n/a
have a look here. I think this is what you are after
http://support.microsoft.com/default...roduct=acc2000

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:cg**********@news6.svr.pol.co.uk...
I have a query that returns weekly enrollments:

Qrygetweeklycountofdosmoduleenrollments Week Commencing Week Ending Total Bookings
02/04/2004 02/04/2004 0
05/04/2004 09/04/2004 0
12/04/2004 16/04/2004 1
19/04/2004 23/04/2004 2
26/04/2004 30/04/2004 1
03/05/2004 07/05/2004 1
10/05/2004 14/05/2004 1
17/05/2004 21/05/2004 0
24/05/2004 28/05/2004 2
31/05/2004 04/06/2004 1
07/06/2004 11/06/2004 1
14/06/2004 18/06/2004 0
21/06/2004 25/06/2004 3
28/06/2004 02/07/2004 3
05/07/2004 09/07/2004 4
12/07/2004 16/07/2004 2
19/07/2004 23/07/2004 3
26/07/2004 30/07/2004 6
02/08/2004 06/08/2004 4
09/08/2004 13/08/2004 2
16/08/2004 20/08/2004 15
I would like to get a running sum of the total bookings column I am not sure how to do this.

Many regards in advance
Peter
SQL at present
:
SELECT Min(Qrygetdailycountofdosmoduleenrollments.Date) AS [Week
Commencing], Max(Qrygetdailycountofdosmoduleenrollments.Date) AS [Week
Ending], Sum(Qrygetdailycountofdosmoduleenrollments.[Number of Module
Enrollments]) AS [Total Bookings]
FROM Qrygetdailycountofdosmoduleenrollments
GROUP BY Qrygetdailycountofdosmoduleenrollments.[Week Number];

Nov 13 '05 #6

P: n/a
The idea wolud be something like this:

SELECT
Qrygetdailycountofdosmoduleenrollments.[Week Number],
Min(Qrygetdailycountofdosmoduleenrollments.Date) AS [Week
Commencing],
Max(Qrygetdailycountofdosmoduleenrollments.Date) AS [Week Ending],
Sum(Qrygetdailycountofdosmoduleenrollments.[Number of Module
Enrollments]) AS [Total Bookings]
(SELECT SUM([Number of Module Enrollments]) FROM
Qrygetdailycountofdosmoduleenrollments AS GDT WHERE GDT.[Week Number]
<= Qrygetdailycountofdosmoduleenrollments.[Week Number]) AS
RunningTotal
FROM
Qrygetdailycountofdosmoduleenrollments
GROUP BY
Qrygetdailycountofdosmoduleenrollments.[Week Number];

I haven't tried it, because i didn't feel like creating and filling a
table, so if you can't get it working mail me a sample db. This will
get pretty slow if the table grows, because it does a subqeury for
every record in the final view. Access is not very good at optimising
such things. So if the underlying table becomes big you'll want to put
a where clause in Qrygetdailycountofdosmoduleenrollments. Also,
becuase i don't know the underlying tables for qeury
Qrygetdailycountofdosmoduleenrollments i used it instead, quering the
underlying table directly would probably have better performance.

Regards,

GJ

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message news:<cg**********@news6.svr.pol.co.uk>...
I have a query that returns weekly enrollments:

Qrygetweeklycountofdosmoduleenrollments Week Commencing Week Ending Total
Bookings
02/04/2004 02/04/2004 0
05/04/2004 09/04/2004 0
12/04/2004 16/04/2004 1
19/04/2004 23/04/2004 2
26/04/2004 30/04/2004 1
03/05/2004 07/05/2004 1
10/05/2004 14/05/2004 1
17/05/2004 21/05/2004 0
24/05/2004 28/05/2004 2
31/05/2004 04/06/2004 1
07/06/2004 11/06/2004 1
14/06/2004 18/06/2004 0
21/06/2004 25/06/2004 3
28/06/2004 02/07/2004 3
05/07/2004 09/07/2004 4
12/07/2004 16/07/2004 2
19/07/2004 23/07/2004 3
26/07/2004 30/07/2004 6
02/08/2004 06/08/2004 4
09/08/2004 13/08/2004 2
16/08/2004 20/08/2004 15
I would like to get a running sum of the total bookings column I am not sure
how to do this.

Many regards in advance
Peter
SQL at present
:
SELECT Min(Qrygetdailycountofdosmoduleenrollments.Date) AS [Week
Commencing], Max(Qrygetdailycountofdosmoduleenrollments.Date) AS [Week
Ending], Sum(Qrygetdailycountofdosmoduleenrollments.[Number of Module
Enrollments]) AS [Total Bookings]
FROM Qrygetdailycountofdosmoduleenrollments
GROUP BY Qrygetdailycountofdosmoduleenrollments.[Week Number];

Nov 13 '05 #7

P: n/a
Oops:

There should be a comma before select like so:

.... Total Bookings],
(SELECT SUM([Number of Module ...

regards GJ

gj******@hotmail.com (G.J. v.d. Kamp) wrote in message news:<d4**************************@posting.google. com>...
The idea wolud be something like this:

SELECT
Qrygetdailycountofdosmoduleenrollments.[Week Number],
Min(Qrygetdailycountofdosmoduleenrollments.Date) AS [Week
Commencing],
Max(Qrygetdailycountofdosmoduleenrollments.Date) AS [Week Ending],
Sum(Qrygetdailycountofdosmoduleenrollments.[Number of Module
Enrollments]) AS [Total Bookings]
(SELECT SUM([Number of Module Enrollments]) FROM
Qrygetdailycountofdosmoduleenrollments AS GDT WHERE GDT.[Week Number]
<= Qrygetdailycountofdosmoduleenrollments.[Week Number]) AS
RunningTotal
FROM
Qrygetdailycountofdosmoduleenrollments
GROUP BY
Qrygetdailycountofdosmoduleenrollments.[Week Number];

I haven't tried it, because i didn't feel like creating and filling a
table, so if you can't get it working mail me a sample db. This will
get pretty slow if the table grows, because it does a subqeury for
every record in the final view. Access is not very good at optimising
such things. So if the underlying table becomes big you'll want to put
a where clause in Qrygetdailycountofdosmoduleenrollments. Also,
becuase i don't know the underlying tables for qeury
Qrygetdailycountofdosmoduleenrollments i used it instead, quering the
underlying table directly would probably have better performance.

Regards,

GJ

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message news:<cg**********@news6.svr.pol.co.uk>...
I have a query that returns weekly enrollments:

Qrygetweeklycountofdosmoduleenrollments Week Commencing Week Ending Total
Bookings
02/04/2004 02/04/2004 0
05/04/2004 09/04/2004 0
12/04/2004 16/04/2004 1
19/04/2004 23/04/2004 2
26/04/2004 30/04/2004 1
03/05/2004 07/05/2004 1
10/05/2004 14/05/2004 1
17/05/2004 21/05/2004 0
24/05/2004 28/05/2004 2
31/05/2004 04/06/2004 1
07/06/2004 11/06/2004 1
14/06/2004 18/06/2004 0
21/06/2004 25/06/2004 3
28/06/2004 02/07/2004 3
05/07/2004 09/07/2004 4
12/07/2004 16/07/2004 2
19/07/2004 23/07/2004 3
26/07/2004 30/07/2004 6
02/08/2004 06/08/2004 4
09/08/2004 13/08/2004 2
16/08/2004 20/08/2004 15
I would like to get a running sum of the total bookings column I am not sure
how to do this.

Many regards in advance
Peter
SQL at present
:
SELECT Min(Qrygetdailycountofdosmoduleenrollments.Date) AS [Week
Commencing], Max(Qrygetdailycountofdosmoduleenrollments.Date) AS [Week
Ending], Sum(Qrygetdailycountofdosmoduleenrollments.[Number of Module
Enrollments]) AS [Total Bookings]
FROM Qrygetdailycountofdosmoduleenrollments
GROUP BY Qrygetdailycountofdosmoduleenrollments.[Week Number];

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.