Making Stock Database HELP!

[c11riz]

I am looking to design a simple stock database for work. This is just an idea at the moment, unless I can get my head round this problem.

I have throught that I can have 3 tables

NEW STOCK(this is where you enter all new and existing stock is)

USED STOCK(this is where all the stock that has been sold \ used for whatever reason is entered)

ACTUAL STOCK(this is where the items in NEW STOCK have the items sold in USED STOCK deducted from them to give you the ACTUAL STOCK)


PLEASE HELP ME, am I completely on the wrong line or can I do this?
Would be good if I didnt have to use SQL, VBA etc...

 

[WIS]

I am looking to design a simple stock database for work. This is just an idea at the moment, unless I can get my head round this problem.

I have throught that I can have 3 tables

NEW STOCK(this is where you enter all new and existing stock is)

USED STOCK(this is where all the stock that has been sold \ used for whatever reason is entered)

ACTUAL STOCK(this is where the items in NEW STOCK have the items sold in USED STOCK deducted from them to give you the ACTUAL STOCK)


PLEASE HELP ME, am I completely on the wrong line or can I do this?
Would be good if I didnt have to use SQL, VBA etc...

You only need to set up 1 table eg tblStock.

NEW STOCK Enter all your details on stock. Have a Primary field StockNoID, Autonumber as well.

USED STOCK In tblStock put field DateSoldOrUsed, Date. When an item reaches USED STOCK status, then a date is entered into this field.

ACTUAL STOCK You can get this with a query, criteria DateSoldOrUsed Is Null.

 

[Len Boorman]

There are a number of threads on Stock and Inventory.

Not sure that a "simple" system is possible.

If you are only going to use one table then use Excel

Three tables will not work. Design would not be normalised and you will in all probability run into lots of problems.

Recommend reading the threads on Inventory Control and then decide what you want to do

Len