Running total / Form

[m.gomez36]

I'll try to make this short but sweet.

I am trying to keep track of inventory and I have a table w/ a base amount of each material. I have a form for work orders that includes how much material will be used for the order. how can I subtract or add the material used or gained from these forms to my "inventory table"???



If there is an easier way I will gladly try. Thanks in advance for any help.

[rpeare]

There's no 'short' answer for inventory control databases but here are some things you will want to do:

1. Have a table for your Purchase Order main information (customer, date, etc)
2. Have a table for your purchase order detail (materials, quantities, etc)
3. If you record your purchases of materials with the vendor's PO's they can go into this same structure (no need to add another form that adds data to a different table if the data collected is similar)
4. Have a table that has all your items listed
5. Have a table that stores your materials and the starting balance of those materials (you can, by default assume they are zero then on your initial month end inventory 'add' materials to equal your materials on hand)

to get a 'current balance' of materials you would then sum all receipts and subtract all disbursements of materials up to the time of the PO (or other reporting period like monthly financial reporting, reordering volume warnings, etc).

You do ***NOT*** want to update a table with your quantity on hand every time you use/add to your stockpile. It is a very, very bad method for keeping track of your inventory as you have absolutely zero audit trail.

For instance let's say you receive 100 pounds of Item X, but accidentally enter it as 100 pounds of Item Y. At your month end or year end inventory you'll have an accumulation of these errors with no way to track them down or tie them to your original purchases or sales.