Auto allocate production

Hi, I feel I should be able to work this out but it is not happening for me.

We take orders for and manufacture product; we may take orders for 200 units but only manufacture 150. I need to automatically allocate product to a weeks production list.

We have an [color:red]Orders file populating an Items file; a record in the [color:red]Items file may show Qty = 20 Model = A Color = Blue. There will be multiple records linked to each Order.

We also have a [color:red]Production file with a record for each week and the number of each model can be produced in that week; numbers are normally consistent but some weeks have holidays or scheduled maintenance.

I want to create a production list each week with the breakdown of models and colors which equals the production of that week. The trick is some orders will cross the cutoff of production and need to be carried over to the next week [ie 5 being manufactured this week and the remainder next week].

The other requirement which should come from resolving the above is to be able to project when a customer will recieve their order.

I hope that is clear and thanks for any advice

Gail