Inventory Control Design RRS feed

  • Question

  • I am designing the Inventory Control portion of our database so that (for the most part) additions are added from Purchase Orders that we send to suppliers and subtractions would be subtracted based on Orders (sales) we process. Of course I would use the corresponding tables in the database to pull that data directly from the Orders & Purchase Orders Tables. The advantages of this method was explained to me previously vs collecting all inventory transactions in a transaction table. This made a lot of sense, especially in light of potential corrections or adjustments to an order or purchase order which occur all too often. So this method of using Sales & Purchase Orders will take care of that.

    But, I can see that there may be still be other transaction types that may occur that aren't accounted for with those two processes, like returns, damage, and maybe a few others I can't think of right now. So should I still be designing an Inventory Transaction Table to hold those transactions and probably an Inventory Transaction Type Table even if I am using the two tables from Orders (Sales) & Purchase Orders? Because this way, the calculations for Available Qty would have to come from multiple tables.

    Also, regarding, Purchase Orders, though we want to know what's on order from a supplier, the calculation of the actual Available Qty can only really happen when any or part of that order is delivered to the warehouse. So I think I need to track deliveries to the warehouse as well and note them separately from what is ordered in a purchase order. Does that sound correct?

    Tuesday, June 16, 2020 9:36 PM

All replies

  • Inventory management is a complex subject. Forums like this are not the best way to discuss them.

    Maybe better would be to discuss this with a business analyst, or with a very experienced developer.

    -Tom. Microsoft Access MVP

    Wednesday, June 17, 2020 2:16 AM