Weekly Running Totals

[Ryan Nelson]

Hello,

I've got a simple database used to schedule deliveries which contains one
main table of orders. The 2 main fields are the scheduled delivery date
(scheduled) and order value (value). I need to present the user with a
running total for both number of orders and order value that has a schedule
delivery date in the current working week (Mon-Fri). If I could also
generate similar totals for the month so far that would also be useful. Any
ideas how I might achieve this?

 

[Michel Walsh]

Hi,


A running total starting at 0 each Lundy?


SELECT a.scheduled, a.order, LAST(a.value) As thisOrderValue, SUM(b.value)
As runningValueForTheWeek
FROM table AS a INNER JOIN table AS b
ON a.scheduled >= b.scheduled AND 0=DateDiff("ww", a.scheduled,
b.scheduled)
GROUP BY a.scheduled, a.order




Hoping it may help,
Vanderghast, Access MVP

 

[Ryan Nelson]

Thanks Michel, I will try that code.

Hi,


A running total starting at 0 each Lundy?


SELECT a.scheduled, a.order, LAST(a.value) As thisOrderValue, SUM(b.value)
As runningValueForTheWeek
FROM table AS a INNER JOIN table AS b
ON a.scheduled >= b.scheduled AND 0=DateDiff("ww", a.scheduled,
b.scheduled)
GROUP BY a.scheduled, a.order




Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


Cheese... should have said Monday


Vanderghast, Access MVP