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?
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.
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.

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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.
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.
SteveL13Author Commented:
I decided to go with one table and two input forms.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.