Best way to handle incoming and outgoing inventory
This is something I've wondered about for some time now. In an inventory database, is it best to have two tables/forms... one for incoming and one for outgoing inventory?
Or is it best to have just one table with two forms? And have the system flag the incoming and/or outgoing inventory with checkboxes or option groups?
Microsoft AccessCOBOL
Last Comment
SteveL13
8/22/2022 - Mon
John
Incoming inventory represents purchases for later sale.
Outgoing inventory represents sales of earlier (perhaps weeks earlier) purchases.
I prefer a single table. You will need fewer supporting objects (forms/reports/queries) if you use a single table and since they are essentially the same action with a different sign, I keep them together. You'll find as you expand your design, that you actually have additional inventory transactions and you certainly don't want to end up creating additional tables for each inventory action, so my vote is one table and that is what I have used for all the inventory applications I have created from COBOL/IMS in the 70's to Access/SQL Server today. Each record has a transaction type and the quantity field contains the natural sign. So dispersals would be negative and receipts would be positive and the various adjustments could be either + or -. If your stock count produces a count of 10 and the inventory record says 7, the difference would be +3. If the count was 7 and the inventory record says 10, then the adjustment transaction would be for -3.
John
I understand the above, and of course, there is no best method depending on circumstances. You would have to be aware of time differences of ins and outs. That could probably be managed in a single table as well.
Outgoing inventory represents sales of earlier (perhaps weeks earlier) purchases.
I would keep the two things separate.