1 Reply Latest reply on Jul 20, 2009 12:51 PM by comment_1

    Relationship Hierarchies / Multi-Layered Sub Groups

    karelifor_1

      Title

      Relationship Hierarchies / Multi-Layered Sub Groups

      Post

      I'm not even sure how to name this issue! I haven't been able to find a similar thread using terminology that I would use . . . please help!!

       

      I am building a database to manage our printed catalog. I am trying to create a layered structure that mirrors the current content of the catalog but I am having some difficulties getting the info to display properly in the chosen sort order. Here is an example of the current structure:

       

      Tail Lights & Related (Product Group)
      Chevrolet Tail Light Assemblies (Sub Group A)
      Passenger Cars (Sub Group B)
      Chevrolet Pickups (Sub Group B)
           Black Body, Chrome Rim (Sub Group C)
           Chrome Body, Chrome Rim (Sub Group C)

       

      I have a table for each group (Product, A, B, C).

       

      A relates to Product Group

      B relates to A

      C relates to B

       

      Due to the number of records in sub-groups A-C, I thought it would be cleaner to organize as separate tables linking them to each other.

       

      Here is sample data for all groups:

       

      Product Groups
      Record ID    Sort ID   Heading
      15             1           Tail Lights & Related

       

      Sub Group A
      Record ID    Product ID    Sort ID   Heading
      200            15               1           Chevrolet Tail Light Assemblies
      201            15               2           Chevrolet Parking Lamp Lenses

       

      Sub Group B    
      Record ID    Sub Group A ID    Sort ID    Heading
      25              200                   1            Passenger Cars
      28              200                   2            Chevrolet Pickups

       

      Sub Group C
      Record ID     Sub Group B ID    Sort ID     Heading
      15               28                    1             Chrome Body, Chrome Rim
      40               28                    2             Black Body, Chrome Rim

       

      So, it's not just a matter of displaying the info in an alphabetized structure - each record has a unique Record ID but it may have identical group IDs. I found a thread that helped me to get Sub Group C to show up using Sub Group C records for the report layout. However, two problems occurred. Unless I added relationship and a field (or multiple fields) to include the related Product Group and Sub Groups included in Sub Group C, I couldn't get the data to display properly. Only when I added Product Group ID to Sub Group C did I get that part to display. But the previewed sort basing the sub summaries on the Sort ID for each group did not display the correct IDs. It sorted ok, but the numbers in the Sort ID and Record ID field displayed SOMETHING else.

       

      Could someone please advise on whether or not I've created too many tables. And if I have NOT created too many, how I can get the relationships to work so that all group hierarchy sorts by the Sort ID.

       

      Any help is truly appreciated!! :smileytongue: