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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnBusiness Consultant (Owner)Commented:
Incoming inventory represents purchases for later sale.
Outgoing inventory represents sales of earlier (perhaps weeks earlier) purchases.

I would keep the two things separate.
Gustav BrockCIOCommented:
There is no "best" method, it all depends.

You can have a transaction-style table where you record all movements in stock, positive for increase (purchase), negative for decrease (sale or loss) and the type of transaction.
However, often the type of handling is very different between sale and purchase which can make it handy to have separate tables.

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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

JohnBusiness Consultant (Owner)Commented:
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.
there is no best method depending on circumstances
I would have to disagree.  It is separating the two that causes the timing issue.  When both are in the same table the autonumber would correctly sequence them.  If you were creating a check register you wouldn't create two tables would you?  One for deposits and another for checks and debit card withdrawals?

There is no downside to using a single table since queries can be used to separate by transaction type if you only wanted to look at one type of transaction.  And there is downside to using two tables.  You would need separate forms if you wanted to do manual data entry and you would need to use union queries if you wanted the data in a single recordset.  You would also need to rely on a timestamp to help with ordering records rather than being able to rely on a sequentially assigned autonumber.  Since inventory applications are essentially concerned with balance on hand, that is easier to derive with all the transactions in the same table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
I decided to go with one table and two input forms.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.