Weekly Rolling Total Query

[Guest]

What is the expression to calculate a weekly rolling total?

Week Amt Total
1/1/07 100 ---
1/7/07 125 225
1/14/07 30 255
1/21/07 65 320

Thanks!

 

[Allen Browne]

From your example, it looks like you already have a query that gives you the
amount for each week, and now you want the rolling total as well. Use a
subquery to get that.

For any week the rolling total is just the sum of all amounts since the
beginning of this year.

Here's an example of a subquery that gives the year-to-date value based on
months:
http://allenbrowne.com/subquery-01.html#YTD
You will need to modify it to suit your field names, and by weeks instead of
months.

 

[Michel Walsh]

You can also do it with a join:


SELECT a.week, SUM(a.amt), SUM(b.amt)
FROM myTable As a INNER JOIN myTable AS b
ON a.week >= b.week
GROUP BY a.week



Where a and b are two aliases to your table. Those records referred by 'b'
are records that are less than, or equal to, the one referred by 'a' and
since it represents 'date and time', less means, in that context, occurring
previously, in time. So, for a given a.week (as kept by the GROUP BY), the
selected fields are those of the sum of amt occurring that week (the
solution allows multiple records, per week) with SUM(a.amt) and also, the
sum of amt of all records occurring previously, SUM(b.amt), supplying
effectively your running sum.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks for all the help! It works now!

You can also do it with a join:


SELECT a.week, SUM(a.amt), SUM(b.amt)
FROM myTable As a INNER JOIN myTable AS b
ON a.week >= b.week
GROUP BY a.week



Where a and b are two aliases to your table. Those records referred by 'b'
are records that are less than, or equal to, the one referred by 'a' and
since it represents 'date and time', less means, in that context, occurring
previously, in time. So, for a given a.week (as kept by the GROUP BY), the
selected fields are those of the sum of amt occurring that week (the
solution allows multiple records, per week) with SUM(a.amt) and also, the
sum of amt of all records occurring previously, SUM(b.amt), supplying
effectively your running sum.



Hoping it may help,
Vanderghast, Access MVP