Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    10

    Question Unanswered: Create table with Data from several records

    I have a table with these fields:
    ProductID
    ProductCode
    Description
    Manufacturer
    Model
    Price

    Since one product may be compatible with several different models, the product code is duplicated for every model. I need to create a new table so I can upload the list on my website without having the product codes duplicated.

    The new format should be like this:

    ProductID
    ProductCode
    Description
    Manufacturer
    Model
    Price
    Category1
    Category2
    Category3
    Categoryn

    What I want to do is to create a new table from the old one, and for everytime a product is duplicated, it will add itself like this in one of the category fields: maincategoryname/subcategory/Manufacturer/Model

    I am not an expert, I just started to work with access a few months ago. Please Help me.
    Last edited by byrke; 08-13-04 at 11:26.

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I would suggest a different design. Have a product table and a models table. Create a third table, perhaps Product_Model_Compatibility which serves as a link between the two tables. Every time a new compatibility comes to light, you are simply adding a new record to the third table rather than changing the table structure. Changing your table design constantly is not a good idea.

  3. #3
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    10
    I already have a product table. What I want to do is append those data in a new table without having the product codes duplicate. In the new table, I would have a few fields called Category1, Category2, Category3..... Every model would be a category on its own, instead of a new record.
    I was thinking about doing it manually, but I have over 100,000 parts in the source table. I was thinking if I can use a code to look at the source table, then extract the part number from the first record, then append the data in the new table, and everytime the product code is duplicated, it would look at the model, and append it in one of the category fields in of the record with its respective product code like this: maincategoryname/subcategory/Manufacturer/Model.
    The main category and the subcategory will be fields from the source table.
    I really need to get this to work.

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    You are trying to use hierarchal database design. That was pretty much replaced with the relational database design in the late 70's and 80's. Access is a relational database. The table structure is the basis for all of your queries, forms and reports. I don't understand the relationship between your products and your models. What is the difference between productID, ProductCode, category, and model? Can you give me some sample data:

    ProductID ProductCode Description Manufacturer Model Price
    "Prod1","Code1","Widgets - Right Handed","Acme","WRH",19.99
    "Prod2","Code2","Widgets - Left Handed","Acme","WLH",19.99
    "Prod3","Code3","Widgets - Either Hand","Acme","WBH",19.99

    It is important to understand the physical world and business practices before trying to represent them in a database.

  5. #5
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    10

    Sample

    The product ID is autogenerated.

    ProductID ProductCode Description Manufacturer Model Price
    "001" "101899-001" "DC Adapter" "Compaq" "Armada E500S" "99.99"
    "002" "101899-001" "DC Adapter" "Compaq" "Armada E515" "99.99"
    Last edited by byrke; 08-13-04 at 12:35.

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Since your goal is to get rid of duplicate product codes, (even though the Distinct keyword in a query would produce this) and you want to change your table structure, I would still suggest 3 tables:


    Table Products:
    ProductCode, Description, Manufacturer, Price

    Table Models
    Model, Discontinued, ...

    Table ProductModels:
    ProductCode, Model

    These tables would allow you to link more models to products without the hassle of changing the table structure. The problem with changing a table's structure is then all queries, forms and reports have to be redesigned.

    Just in case this is a one time occurance, you may want to look into using a Make Table query (rather than the select, delete, append, or update queries you may already be used to).

Posting Permissions

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