Linking number fields for inventory/order tracking

[jw195]

Hello,

I posted a few days ago looking for some help for blocking duplicate records in an inventory & order tracking data base. I was kindly helped quickly and have been making progress slowly but surely.

I am now at the point where I (think) have all the tables I need and the data is normalized (again, I think). I now need to make the database recognize when an order has been entered and deduct the inventory for an order from its respective product. I have a few ideas on how to make it work, however I don't want to try sloppy techniques and wreck the whole thing...

I'll attach screenshots of my relationships and tables for support.

So in the Orders table, which is linked to the Order_Form Table I need the order of 15 12"BegoniaFancyLeaf to be removed from the "beginning_inventory" Field in the Master_Inventory table.

Looking forward to your responses, thanks again,

John

 

[pbaldy]

I wouldn't try to maintain the balance, I'd calculate it from transactions as needed.

 

[jw195]

What? That wouldn't make any sense. It would take an insane amount of time to sort through every order and calculate the amount of product ordered for each different product and manually do that arithmetic.

I would be better off doing the whole thing in excel than that.

 

[pbaldy]

Maybe you'll believe Allen:

http://allenbrowne.com/AppInventory.html

I have tables with millions of records, still calculate balance on the fly.