SteveL13
asked on
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I decided to go with one table and two input forms.
Outgoing inventory represents sales of earlier (perhaps weeks earlier) purchases.
I would keep the two things separate.