AnsweredAssumed Answered

Relationship Hierarchies / Multi-Layered Sub Groups

Question asked by karelifor_1 on Jul 20, 2009
Latest reply on Jul 20, 2009 by comment_1


Relationship Hierarchies / Multi-Layered Sub Groups


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: