Accumulative Demand or Calculated Values

[jlgray0127]

Good morning.
I have a data base I'm using for tracking inventory and orders.
I want it to allocate stock from my inventory to the order, based on ship dates.

I have A1A part number, with 10 pieces.

I have orders for:
Date Order Qty On Hand Qty
10


1/1/15 2 8
1/5/15 3 5
1/8/15 4 1
1/8/15 3 -2

I need for my planning system to acknowledge that order 1, order 2 and order 3 are ok to pick, and order 4 is on order. I have all this set up already, with the exception, I am manually changing the order status, based off the amount of stock I have and the date is due to ship.

I need help only with the logic of, 1/1/15 is sooner than 1/5/15 and there is stock available, etc, until it runs out. Does that make sense? I am not able to attach the database, due to multiple back ends, and it's size, complexity, etc... Thank you in advance.

[rpeare]

Why is the quantity on hand being stored? You should be able to calculate what's on hand based on incoming/outgoing product modified by inventory counts if you do them regularly.