Help of Designing Tables and Relations (Newbie)

[Mabalasik]

Hello to all!

I'm Ronald and a newbie in MS Access. I am trying to create our stock management list cause currently we're just using excel spreadsheet to manage our products list. I'm just starting to create a database in access.

My first table is ProductList with fields ProductCode(PK), ProductName, OrderClientType, TypeOfUse. This table will be seldom touched or manipulate.

The second table is called BatchNumCode/Quantity consists of fields BatchID (my PK, set to auto increment), ProductCode (my FK), BatchNumCode and Quantity. This table will be updated from time to time as products come and go almost every 3 to 4 days only and a product can have 2 or 3 batch number codes.

My question is, in table BatchNumCode/Quantity, ProductCode will be deleted and replaced by a new batch number very often. Would it be ok if i use a primary key with autonum/increment or ramdom considering that entries will be deleted and replaced from time to time ? And how will this affect in the long run.



i attached here the table relation. thank you and sorry for my english.

[ranman256]

I don't think you should delete and add records.
you should add new ones.,but if already exists add to the quantity.

[ssanfu]

Welcome to the forum!

My question is, in table BatchNumCode/Quantity, ProductCode will be deleted and replaced by a new batch number very often. Would it be ok if i use a primary key with autonum/increment or ramdom considering that entries will be deleted and replaced from time to time ? And how will this affect in the long run.

First thing. Object names in Access should be only letters and numbers. No spaces, special characters (exception is the underscore) or punctuation.
Having "BatchNumCode/Quantity" as a table name not a good name because of the slash. Better would be "BatchNumCodeQuantity" or "BatchNumCode_Quantity"
Second, read this:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

So, yes, it is OK to have a PK field as an Autonumber type. Virtually all of my tables have an autonumber as the PK field.

Would you explain more why the "ProductCode" will be deleted and replaced by a new batch number? Could you just change the batch number & quantity?

Also I noticed there is not a date field in table "BatchNumCode/Quantity". Is a date important when adding/changing the "ProductCode" / "BatchNumCode" / "Quantity"?