stock control design

[tizwaz]

I need to design a database to track available stock of uniform items.

I want to have details of how many of each item eg shirt size 40" are in stores plus details of what uniform items have been issued to cadets.

Also from time to time we will order more of certain items.

What is the best structure? Would I be better storing available stock as a calculated field or holding it in a table?

 

[SkipVought]

tizwaz,

An item would have OnHandQuantity as a value at a point in time.

As stock is issued, you adjust OnHandQuantity by subtracting values, and as stock is replenished, you adjust OnHandQuantity by adding values.

Don't know what you mean by a calculated field.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[tizwaz]

sorry - I meant having on hand as a calculated field in a form. Not sure how to go about it though

 

[sillysod]

storing the field is bad design.

however you can do it this way if you dont want to get too complex

the ideal solution is to have a stock transaction table that records the history of each stock line.
the qty on hand is then calulated by using the formula
opening stock add purchases less goods sold

 

[16051987]

does any1 have any link or somethign taht can instruct me on how i should design a stock control system on Microsoft Access , thankx

 

[willir]

Here is a list of some fundementals...
Fundamentals of Relational Database Design
Download document
Read on-line (HTML)


Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

You can also look at the Nortwind database

Somethig to get you started...

tblItems
ItemID - primary key
ItemDesc

tblSizes
SizeCode - primary key

Discussion: You do not realy need a size table but it may help standardizing sizes, and not having to create value lists in your list and combo boxes.

tblCustomer
CusotmerID - primary key
CustomerName

Discussion: Customer can refer to a compnay or person depending on the nature of your business

tblInventory
InventoryID - primary key
ItemID - foreign key to tblItem
SizeCode
CustomerID - foreign key to tblCustomer

Discussion: If the CustomerID field has an entry, the item is assigned. If the CustomerID field is blnak / null, then it may be considered "OnHand". You can get fancy and use a CustomerID referencing OutForRepairs and such.

Richard