Transaction table setup to adjust inventory

[jesswoo]

Hello,

I have a database that I use to put in orders for our shop and keep track of our part informations as well as paint and packing materials. Everything works good on this but I am trying to create a table for adjusting quantities on hand for packing material based on the part quantities and for adjusting paint in stock based on information given to me after the job is run.

There is already a relationship between parts and packing material as well as parts and paint. When I put an order in I would like to have it adjust out that many packing materials that are related to that part. Once a job is run I need to be able to adjust out the amount of paint used.

For the paint side of this I want it to track the paint used by order, we are trying to get a grasp on how much paint we are using for parts so it is important for me to know how much and when.

I have a little bit of an idea on this but not really sure. I am thinking I need a table that connects paint to orders and has quantities in it, then create a query and do the calculations from there... I do not know how to accomplish that but it sounds like it could be right...

All help is greatly appreciated, I am still trying to figure out a lot about access.

 

[jesswoo]

I do not understand Allen Brownes description of how to do this and I cannot find a model on that one site that a lot of people send others to.

All I want is for packing materials to be added and subtracted and to add or delete paint and see its usage by order. There is no financial information or anything else. Just what we have.