Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

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?
Avatar of John
John
Flag of Canada image

Incoming inventory represents purchases for later sale.
Outgoing inventory represents sales of earlier (perhaps weeks earlier) purchases.

I would keep the two things separate.
SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SteveL13

ASKER

I decided to go with one table and two input forms.