run sum

[Irene]

i have a table call t_time_sheet. i want ot make a query that is based on
this table to do a run sum.

example,
ID Part Date Qty
1 A 1/1/09 100
2 A 2/1/09 100
3 B 1/1/09 100
4 A 31/12/08 100

So i want to do a query, that the result want to come out as

ID Part Date Qty RunningSum
4 A 31/12/08 100 100
1 A 1/1/09 100 200
2 A 2/1/09 100 300
3 B 1/1/09 100 100

can do it by query? I want the result come out by date first then just by ID
to calculate the run sum by part. anybody can help me? thanks! urgent!

regards,
Irene

 

[Allen Browne]

Use a subquery to get the running sum.

Type an expression like this into the Field row in query design (all of it
on the Field row):
RunningSum: (SELECT Sum(Qty) AS HowMany
FROM t_time_sheet AS Dupe
WHERE Dupe.Part = t_time_sheet.Part
AND Dupe.[Date] <= t_time_sheet.[Date])

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Note that if you had 2 deliveries for the same part on the same date,
results might be what you expect.

Also, DATE is a reserved word, so can cause you problems. Enclosing it in
square brackets, with the table prefix (as above) should get you off the
hook for this one, but there are cases where it may not work as expected.
You might consider turning off Name AutoCorrect, and renaming it to
something like DeliveryDate.