Results 1 to 3 of 3

Linking problem of tables.

  1. #1
    Art Vandaley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    1

    Linking problem of tables.

    Hi, I'm new to Access and I would appreciate having help about below point.



    In my project I have several tables including category (Table for a main category and tables for sub categories) tables and a product table. I'm linking tables for cascading categories and products. I have a result something like below:

    Category1

    Category2
    ---Category2_1
    ------Product_A
    ------Product_B

    Category3
    ---Category3_1
    ------Product_C
    .
    .
    .
    .

    What I want is:

    Category1
    ---Product_A
    ---Product_B

    Category2
    ---Category2_1
    ------Product_A
    ------Product_B

    Category3
    ---Category3_1
    ------Product_C

    Category4
    ---Category4_1
    ------Category4_1_1
    ---------Product_B
    ---------Product_C
    .
    .
    .

    I have two problems:

    1) I can't assign same product to more than one category.

    2) I can't assign (or link) same product to different level of categories

    Help please...

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,428
    The pseudo data doesn't help me much. I don't see why you don't just have 1 table for category and 1 table with product and the category primary key is in the product table as a foreign key value. Not sure if that will make sense to you. Perhaps some info on normalization

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  3. #3
    Join Date
    Apr 2017
    Posts
    996
    I think your logic is backward. What are you really doing? Producing/selling categories? Or products really?

    When products are your main thing, then start with products table
    tblProducts: ProductID, ProducCode, ProductName, ...

    Next step - products can grouped, and a product can belong to several different groups. You can name those groups Categories. Categories itself can be grouped into categories of different levels. To make this work, products must be linked with category of lowest level. So you need a table
    tblCatgories0: Cat0ID, Cat0Name, ...
    And a table where product will be linked to different 0-level categories.
    tblProdCat0: ProdCat0D, ProductID, Cat0ID

    We have finshed with products now. But we have to apply the structure to categories. Let's assume you have 3 different category levels.
    tblCategories2: Cat2ID, Cat2Name
    tblCategories1: Cat1ID, Cat2ID, Cat1Name
    And now we add the category structure to 0-level categories:
    tblCatgories0: Cat0ID, Cat1ID, Cat0Name, ...

    So you now have your table structures, and you can start defining relationships when you need them. tblProdCat0 will be like to tblProducts and to tblCategories0, tblCategories0 will be linked to tblCategories1, and tblCategories1 will be linked to tblCategories2.

    And whenever you need to add the next groping level for categories later, you simply add table for next level categories, and add it's PK as FK into current top level categories table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 34
    Last Post: 09-22-2015, 05:40 PM
  2. Replies: 7
    Last Post: 04-22-2013, 02:49 PM
  3. Problem linking two tables(relational)
    By Hairy in forum Access
    Replies: 14
    Last Post: 09-22-2012, 06:28 PM
  4. Replies: 0
    Last Post: 03-04-2011, 10:28 AM
  5. Form - subform linking problem
    By Justin in forum Access
    Replies: 1
    Last Post: 11-11-2010, 11:21 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums