Avatar of SteveL13
SteveL13
Flag 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?
Microsoft AccessCOBOL

Avatar of undefined
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 would keep the two things separate.
SOLUTION
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
PatHartman

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SteveL13

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