Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2017
    Posts
    1

    Unanswered: Newbie question about creation of tables

    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.
    Attached Thumbnails Attached Thumbnails Relationship.jpg  

  2. #2
    Join Date
    Dec 2016
    Posts
    22
    I am also a beginner, but I would think that having a primary key that changes frequently could be problematic.

    I don't have a direct answer, but I do have some questions that may help you decide. I am doing something similar and these are questions I dealt with in setting up my inventory system.

    Would the product key change in both tables?
    If it is changed in "ProductList" would it automatically be updated in "BatchNumCode"?
    If it is changed in "ProductList" does that break the connection?

    Here is my (more complicated set up). You will notice I organized it left, middle and right. The left tables have very few changes made to them, ever. All the work is done in the middle and right tables. Conceptually, it is a big deal for us to change the left tables. And the BarcodeID will only change in a situation of lost items.

    Click image for larger version. 

Name:	relationships.PNG 
Views:	3 
Size:	78.5 KB 
ID:	17245

    I am not sure this helped you or not. I hope it did!

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,476
    Provided Answers: 11
    Quote Originally Posted by mabalasik View Post
    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.
    Just a Tip on Your Tables

    I alway ad the ID to a PK and FK

    only when U are looking at a Table and you see ProdListID U know which Table it related to.
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  4. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,095
    Provided Answers: 17
    You might find it easier to manage if you add a third table:
    ProductList
    ProductCode (PK)
    ProductName
    OrderClientType
    TypeOfUse

    BatchList
    BatchID (PK)
    ProductCode (FK)
    BatchCode
    CreationDate
    ExpiryDate

    BatchStock
    BatchID (Two-part key)
    Location (Two-part key)
    StockQty

    That way, you update the first table when you get new items, the second to create a batch record for stock and the third to hold the current stock level.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •