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?

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

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

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