• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 54
  • Last Modified:

Access/SQL table structure for simple Inventory system

Hi

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.

Thanks
0
Murray Brown
Asked:
Murray Brown
1 Solution
 
Ares KurkluSoftware EngineerCommented:
Hi,

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.

http://www.almyta.com/abc_inventory_software.asp

https://access-templates.com/access+2016/access+database+ecommerce+inventory+tracking+management+templates-609.html
0
 
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Murray,

 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.

Jim.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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

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

Jim.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi Jim. I would be really interested to hear more.
Thanks
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
0
 
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.

Jim.



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
etc

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
WarehouseID
Name
Address1
Address2
City
State
Zip
PhoneNumber
Hours
Manager
etc

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
Qty
UOM
DateCreated - D/T
LastReceipt - D/T
LastIssue - D/T
0
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.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now