Access/SQL table structure for simple Inventory system


I am trying to build a simple inventory system for a company that buys and re-sells a list of products.
I looked at the Access 2007 Northwind database, but was told that it was not a good example.

One table that I assume you can't do without is the Inventory Transactions/Ledger table.

Any advice would be appreciated.

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.

Ares KurkluSoftware EngineerCommented:

I am not sure how simple it can be, there will definitely be Products, Orders, Purchases even suppliers for products, Users if you want to keep track of who is buying what? do you need stock information? We probably need a thorough description of what is required, maybe you can look into some access templates.
Gustav BrockCIOCommented:
This question is way too broad as an "inventory system" can be so many things.

Start with a bing/google search for microsoft access inventory system  - and you'll see pages of options and offerings.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 It's straight forward enough:

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.

In general, you then tack on a locations table (it's in rack, bin, etc in a warehouse).
If you have multiple warehouse to track, you'll need a warehouse table as well.


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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Great answer as always Jim! I needed a basic idea. Thanks very much
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  I'm surprised you closed this already....I'm more than happy to go into detail on each of those if you wish.

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi Jim. I would be really interested to hear more.
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Not sure why, but I can't get to that other question, so I'll post here.

Without specifics of what your doing, I'll give you a general outline and please keep in mind that this could be structured in a number of different ways.

For example, on the transactions table, you might always have the qty as a positive number, and manage different types of transactions through transaction codes (a '09' might mean a PO receipt, and '10' might mean a reversal, but in both cases, the qty's are positive).  In other systems, qty's may be positive or negative to indicate in's and out's.

In the setup below, there is a 'TranType' to show if it's considered an In or Out normally, but the qty can be positive or negative to allow for corrections.

Others might store QOH (Qty On Hand), Committed, On Order, etc in the item master for performance reasons.

The thing to focus on here is the tables themselves and what's in each.

One other point; I generally like to carry a Qty and UOM anyplace there is a qty stored (in inventory, Purchase Orders, Planning, etc).    This allows for more flexibility when moving between say inventory and BOM's.

 You might stock something in dozens, purchase in grosses, but have a BOM that's in thousands.   by keeping both a QTY and UOM, it allows you to scale the QTY up and down on a transaction by transaction basis.

 Also not covered here; expiration dates.   If you have items that expire on the shelf, then you need to account for that.

 Look it over and see if it makes sense.  Should give you a few ideas on how you might want to tackle this and if you provide more details on the type of things your dealing with, I can be a little more focused on something that might be a better fit.


tblINItems - One record per item
ItemID - AutoNumber - PK
Description - Text
InvType - Text - Raw, PWIP, MWIP, FGS
Status - Text - Active, Discontinued, Obsolete, etc.
ProductCode - Text - Class of product
PlannerID - Long - Foreign Key to tblUsers
BaseUOM - Text - Stock keeping unit of measure
ABCCode - Text
Min - Long - Minimum QOH
Max - Long - Maximum QOH
SafetyStock - Long
LastCycleCount - D/T
CycleCountFrequency - Integer (days)
ObsoleteOn - D/T
ReplacedBy - Long

tblINTransactions - One record per in/out of inventory.
InTranID - Autonumber - PK
CreatedOn - D/T
CreatedBy - User ID
EffectiveDate - D/T
TranType - Text - 'I'n or 'O'ut
ProgramID - Text - Program/module that generated the transaction.
DocRefType - Text - ie. Packslip or Traveler if a receipt, cycle count number, work order number, etc - whatever was the cause for the transaction to occur.
DocRefNumber - Text - Reference number
DocSubRef - Long - Within DocRefNumber, line number.
LotID - Long - Foreign Key to tblINLots
TranQty - May be positive or negative
TranUOM - Text

tblWarehouses - One record per warehouse

tblINLocations - One record per allowed location
LocationID - Autonumber - PK
WarehouseID - Long - Foreign Key to tblWareHouses - CK1A
LocationDesc - Text - CK1B

tblINLots - One record per lot
LotID - AutoNumber - PK
LocationID - Long - Foreign Key to tblINLocations
ItemID - Long - Foreign Key to tblINItems
DateCreated - D/T
LastReceipt - D/T
LastIssue - D/T
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

From novice to tech pro — start learning today.