All,
I have been working at this for a bit and wanted to run this by for the CST. Common sense test, and assistance in putting this in VBA code. Where it doesn't bog down the system. like a DCount can. Maybe there is a need for arrays? To see with the figures referenced below, please look at the attached Word document, and the Excel spreadsheet.
1. Starting at Filter where all RowType are MEQUIP (sorts out all the extra rows in the table)
2. Filter where all ParentNodeIDs are the same (breaks down into groups of data), (Count Distinct ParentNodeID for the whole table)
3. Where step 2 = 1 through DMAX(CountDistinct ParentNodeID), filter down to where all RoleIDs are the same (breaks down the Parent Node ID into sub-groups), (Count Distinct RoleID for the whole table)
4. Where step 3 = 1 through DMAX(CountDistinctRoleID) , Find all ParentEquipID = platformID on the same row (This finds the base equipment subbed under the platform), Sort Ascending
5. With the group of UniqueIDs found sort ascending where ParentEquipID = PlatformID , Determine the UniqueID s found = ParentEquipID,
6. Count UniqueID where it is found in ParentEquipID in Step 5 to find where Count is > 1, Count and Store the UniqueID values by ascending order
7. Count ParentEquipID in Step 7 to find where Count = 0, Store UniqueID values, Sort ascending UniqueID
8. Count(UniqueID where ParentEquipID = UniqueID) CntUID=1
9. Count CntUID + 1 through DMAX(CountDistinct UniqueID) of ParentEquipID in Step 8
10. Count CntUID + 1 through DMAX(CountDistinct UniqueID) of ParentEquipID in Step 7
11. Go to step 4, where count = 2 through DMAX(CountDistinctRoleID) , where ParentEquipID = platformID
12. Go to step 3, where count = 2 through DmaX (Distinct RoleID)
13. Go to step 2, where count = 2 through DmaX (Distinct ParentNodeID)
Example:
1. EXCEL spreadsheet
Figure_1
2. ParentNodeID (1222126) = 1
ParentNodeID (1222129) = 2
Figure_2
3. RoleID (1222128) = 1
RoleID (1222131) = 2
RoleID (1222134) = 3
Figure_3
4. ParentEquipID (3589359) = 1
ParentEquipID (3589363) = 2
ParentEquipID (3589364) = 3
ParentEquipID (3589365) = 4
Figure_4
5. ParentEquipID (3589359) = 1
ParentEquipID (3589384) = 2
Figure_5
6. ParentEquipID (3589384) = UniqueID (3589384)
7. ParentEquipID <> UniqueID (3589375)
ParentEquipID <> UniqueID (3589385)
ParentEquipID <> UniqueID (3589386)
ParentEquipID <> UniqueID (3589393)
ParentEquipID <> UniqueID (3625368)
Figure_6
8. UniqueID (3589384) = 1, CntUID =1
9. UniqueID (3589385) = 1, CntUID = 1 + 1 = CntUID = 2
UniqueID (3589386) = 2, CntUID = 1 + 2 = CntUID = 3
Figure_8
10.ParentEquipID <> UniqueID (3589375) = 1, CntUID = 3 + 1 = CntUID = 4
ParentEquipID <> UniqueID (3589393) = 2, CntUID = 3 + 2 = CntUID = 5
ParentEquipID <> UniqueID (3625368) = 3, CntUID = 3 + 3 = CntUID = 6
12. Go to step 4, where count = 2 through DMAX(CountDistinctRoleID) , where ParentEquipID = PlatformID
13. Go to step 3, where count = 2 through DmaX (Distinct RoleID)
14. Go to step 2, where count = 2 through DmaX (Distinct ParentNodeID)
Figure_14