Create a running total

[PRyan]

Hello there, I`m new to this forum but I`ve been using Access for quite a while now (I prefer working in 2003).

I`ve got a task which I can`t seem to find an answer for.
I have a list of sales orders with various items in them, some of these sales orders contain the same items.


I`m trying to find a way that the available stock can be displayed against each sales order item but this available stock needs to be the stock left after the previous (older date) sales order item quantity has been removed.
So it would look something like:

Sales Order	Item	Qty	Stock
A1	X	10	50
A2	Y	25	150
A3	X	15	40
A4	X	10	25

I hope I`ve made myself clear, any ideas?

[ranman256]

make a report,
add an textbox of what you want to sum,
set the property RUNNING SUM = TRUE
set to sum OVER GROUP, or ALL, etc

[June7]

Unfortunately, I don't think textbox RunningSum will accommodate this requirement because the running sum needs to differentiate between Items across Orders. If the records are sorted and grouped by Item then possibly could calc a running sum by Item but you want to sort by Sales Order and list items under each.

You also want each record to show net stock after deduction of Qty from previous record. This could be achieved by adding the current record Qty to the running sum calc. However, as pointed out, records would have to be sorted and grouped by Item.

And then need to calculate a starting Stock value for each Item because I assume this report would be for a restricted period, not all data from beginning of records. Net Stock should be calculated from incoming and outgoing transactions (stock received and stock used). This would normally involve an aggregate query to sum incoming product and an aggregate query to sum outgoing product then joining the two to Products table and subtract. Review http://allenbrowne.com/AppInventory.html. Calculating a starting value would mean determining these aggregate values up to a specified date. Methods could be nested aggregate subquery or DSum() domain aggregate in textbox on report in the Item group header.

[PRyan]

Thanks for the replies.

The stock would come from another query which would sum all stock batches in different warehouses for each item.

[PRyan]

I`ve managed to do this running sum using queries and macros, I`ve never tried to learn code although I have used it in the past if I can`t do something the easy way.
If anyone wants to know how I did it I`ll be glad to tell as I was quite pleased with myself and it only took me all day.