Stock Level trouble

July 4, 200718 yr

I hope someone wants to dive in and help me with this, as I'm having trouble getting the design worked out in my head.

The scenario:

A manufacturer uses various different items to create a finished article. I need to be able to track running totals of all the different items that are used. This is straight forward enough.

However, jobs are first quoted then later confirmed, at which point production begins. Once the job is completed the actual amount of each product used needs to be recorded (as it may not match exactly with the amount quoted). I need to flag if items:

- are out of stock

- are below a minimum reorder level

- will be out of stock or below minimum if the quoted job is confirmed

I can cope with the above. But I need the stock levels to be on a 'running total' basis, e.g when Job 1 was confirmed there was enough stock to complete it, but later jobs will use up the stock. I only want the later jobs to be flagged as 'out of stock' or 'below minimum'. I don't want Job 1 to be flagged when it was not previously. However, I also need the facility for the stock level to be updated (when a new delivery is taken for example) and for this to cascade 'backwards' and update any jobs that were previously flagged.

Still with me?

My thoughts are that I need three 'level' fields in the stock record:

- Actual Physical stock available

- Allocated stock (allocated to confirmed jobs)

- Quoted stock (what will be needed if all quoted jobs are confirmed)

Then I guess some kind of 'Stock_Lines' table to track the 'debits' and 'credits' to the individual items. I can't see how to get running totals in this way, or how I can deal with the cascade 'backwards' requirement as described above.

If anyone can make sense of what I'm saying, and wants to offer some advice, I'd be very pleased to hear it.

Thanks