Running Subtotal

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am looking for a way to subtotal a certain subset in a running subtotal so
If I had 500 of an item and used 6 on day one and 4 on day two and 3 on day 3
and 25 on day four. I will get a subtotal for each day.

I wish I was a programmer sometimes. The situation below contains subsets
where the matierial is the same for multiple orders when that happens I want
to have a running subtotal so I can eliminate the orders that I have enough
material on hand to cover and can associate the exact order to a PO for
future reference.


Below is some sample data that might help someone trying to help me.

I have this information:
Order Material Reqmts qty Already Issued On Hand
1 a 2 0 0
2 b 6 1 2
3 c 8 0 0
7 d 110 0 440
21 d 110 0 440
42 d 110 0 440
55 d 110 0 440
56 d 110 0 440
77 e 2 0 0
81 e 6 0 0
99 f 9 3 2
101 g 6 0 5
251 h 8 0 4

and I want this information
Order Material Reqmts qty Already Issued On Hand Subtotal
7 d 110 0 440 110
21 d 110 0 440 220
42 d 110 0 440 330
55 d 110 0 440 440
56 d 110 0 440 550
77 e 2 0 0 2
81 e 6 0 0 8
 
Try this:

SELECT O1.Order,O1.Material,O1.[Reqmts qty],O1.[Already Issued],O1.[On Hand],
(SELECT SUM([Reqmts qty])
FROM Orders As O2
WHERE O2.Material = O1.Material
AND O2 Order <= O1.Order) AS Subtotal
FROM Orders As O1
ORDER BY O1.Material,O1.Order;
 
Back
Top