Query to split orders up and display how many left it stock

[KenshiroUK]

Hi, is it possible to split orders up based on ID and append to a table with an Order Item ID?

For example a custom phones up and places an order for Product A x 3 and we have 2 in stock. It will then append an Order ID of 20. However I want to split this order up and give it an Order Item ID, with Order ID and tell you how many left in stock.

Order ID
20

Order Item ID Total Left
20-1 ....................1
20-2 ....................0
20-3................... -1 Out of Stock(OoS)

Can this be done at all?

 

[plog]

First, I'm always scared when I hear query action phrases (UPDATE, APPEND, MAKE TABLE). So, hopefully when you use the term "append" you don't have queries that are doing that. Usually those types of queries are used to overcome improper database structure.

For your actualy issue, it sounds like you want a modified running total. Search this forum for that phrase ("running total"), it has been done many times.