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:
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: