I have a Product Table.....and within this table I have created a Relationship with itself.....and my look-up field is called "Parent Product"....for example I have 4 Product records (A, B, C & D)..... example of relationship diagram below..
Product A (Parent to B)
---Product B (Parent to C)
-------Product C (Parent to D)
I have another table in my app called Product Group. Product Groups (Parent) is related to Products (Child).....all 4 of the products (A, B, C & D).....in the products table are related to the same Product Group ("Group 1")...
I would like to have a field on my Products table called [Hierarchy Level]....this field will assign a hierarchy level for each record. (example below)
Hierarchy Level]=4-Product A (Parent to B)
Hierarchy Level]=3----Product B (Parent to C)
Hierarchy Level]=2--------Product C (Parent to D)
Hierarchy Level]=1----------Product D
How can I accomplish this?
*I know that I can create a summary field on my Product Groups table [# of Products] --- this will tell me the total number of Hierarchy Levels....I'm dealing with (4)..but how can I go about assigning each Product its particular Hierarchy Level within their related Product Group.
I appreciate and advice....