running total

[DD]

i need to have a running total in a query.

example

date item qty needed onhand available

03/18/08 1 500 2000 1500
03/19/08 1 1500 2000 0
3/20/08 1 250 2000 -250

so the available column changes based on the available of the prior line. i
want this to continue until the item changes. is this possible.

thanks

 

[Allen Browne]

Use a subquery to get the sum of the previous values for the same item.

You will want type an expression like this into the Field row of your query:

Available: (SELECT Sum([Qty needed]) AS Needed
FROM Table1 AS Dupe
WHERE Dupe.Item = Table1.Item
AND Dupe.[Date] <= Table1.Date
AND Dupe.ID < Table1.ID)

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html