Access Simple Inventory System explained further


Having asked a question about a simple inventory system in Access I was offered the following

1. A item Master table
2. A Inventory Movement table (your Inventory Transactions/Ledger table).  This records all the in's and out's of inventory and why it was done.
3. A lot tracking table.   This is the actual on-hand qty's.

I would like to find out more about this sort of approach.
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
ste5anConnect With a Mentor Senior DeveloperCommented:
Well, not sure what you exactly want to know, but it is pretty simple:

You need a transaction table to document movements. At the same time you need this table to calculate your existing stock (number of items).
In a simple scenario the quantites on hand are simply the SUM() per item of that transaction table, when the prices are constant.

But in the real world you may have a different stock handling like last-in-first- out (LIFO) or first in-first-out (FIFO) with different prices per item over time. In this scenario you need an additional table for the item master to track the price history. And calculating the stock value is no longer a simple query. The same problem arises, when you have different suppliers.
PatHartmanConnect With a Mentor Commented:
Inventory has a lot of subtleties.  It isn't a good application to start your Access career with.  There are big differences between inventory for screws and inventory for serialized items such as cars and computers and helicopters.  Many companies have multiple inventory locations.  Sometimes they have a mix of inexpensive items where all they care about is that they have 20 boxes of 1000 each in stock and other items that cost thousands of dollars each where they need to track by serial number.  When one of these items is sold, you can't just pull a handful out of a box, you have to pull the specific serial numbered item.  One of the inventory applications I built was for a wholesale florist.  The inventory was so volatile, that you didn't know from minute to minute if there were any red roses left.

If this is for your own business, look into Quickbooks.  It may be sufficient for your needs.
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
I agree with the previous two experts...

Try not to re-invent the wheel.

Unless this will be a "very simple" inventory management system (for example, household inventory), things can get quite complicated faster than you can anticipate.

For example, how will you manage?
Back-ordered items
Split shipments/payments
Price changes for an item over time
Write offs
Delinquent payments
Inventory turnover
Inventory Shrinkage/Slippage

How are you dealing with any financial liability if any part of your system is flawed?
Are you managing customer accounts (credit card info, payments), then you need something more secure than MS Access
Are you willing to support this system long term?
What is your skill level with databases?
How much time do you have to complete this database?
What is your time worth?

MS Access is a great tool for creating ad hoc or bespoke database systems.
...In other words for which there exists no off-the-shelf alternative.

You list three basic tables in your original post.
Even a fairly basic "Contacts" database will require at least 6 tables.

What you have here is a very broad "design" question.
These types of questions are more suited for a "discussion" forum than a Q&A forum.

You can get some very good general advice here.
But many experts may not be able to afford to get into the specifics of a design without knowing all the specifics about the entire project.

I am not trying to deter you here ...just stating my opinion, based on working with Access for many years.

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you for the feedback.
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.