Need help with Inventory Management (1 Viewer)

[pernic80]

I have been tasked with creating an inventory management db. My company would like to track the following: company shirts in stock (size, type, color), employees (name, title, email, phone etc), how many shirts of what type were given to what employee, how many shirts we have in stock on any given day, how many shirts have been distributed. In the past I have created an order tracking and an asset tracking DB from scratch so I have some Access experience but I am having trouble setting up this db so that it automatically withdraws items from inventory as they are distributed to employees: Please Help!!
So far, I have set up the following tables:

Table Name:Employees
Fields:
EmployeeID PK
FirstName
LastName
Title
WorkPhone
Email Address
Notes

Table Name: Inventory
Fields:
StockID PK
Item Type (shirt type, shirt size, shirt color, mens/womens/unisex)
Manufacturer
UnitPrice

Table Name: Distribution Information
Fields:
StockID PK
Item Type
Employee ID
Number Issued
Date Issued
Comments

Table Name: Stock Quantity
StockQuantityID PK
Item Type
Number Recieved
Date Received
StockID

Table Name: Stock Control
StockControlID PK
Number Recieved
Date Received
Item Type

Please let me know if these seem like appropriate tables and how to design it so that it pulls automatically from stock if something is taken out and adds to stock as items are recieved.

Thanks in advance!

 

[DCrake]

Don't know why you need a stock qty table these fields should be in your inventory table. You need a transaction tabel to show goods out and in whereupon you can calculate current stock levels as calculations should not be stored in fields.

 

[pernic80]

Once I create the transaction table where should the calculation be entered/arrived at?

 

[DCrake]

In queries, ie total in minus total out = balance

 

[jdraw]

There are a number of inventory related data models at this site.
You may get some ideas from these, and adapt them to your situation.

http://www.databaseanswers.org/data_models/inventory_control_for_retail/index.htm