Generate a Group Table

[JoeZim]

Trying to build a dynamic "Group" table.

Have the following Tables:

Collateral (Table)
CollID
CollName

Account (Table)
AcctID
AcctName

CollateralAccount (Table)
CollID
AcctID

Need to generate a 4th table:
Group (Table)
GroupID
AcctID


What I need to do is setup "Groups" of accounts that have any collateral in common.

Could be cases where 2 or more accounts share same CollID links, but then a 3rd account shares a separate CollID with only one of the original 2 accounts. I'd still consider all 3 accounts being in the same "Group". So at the end of the day, an AcctID can only be a member of one "Group".

Have been researching this for some time and just can't seem to get any momentum.

Sample Data:

Collateral (Table)
CollID CollName
1 CollItemA
2 CollItemB
3 CollItemC
4 CollItemD
5 CollItemE
6 CollItemF


Account (Table)
AcctID AcctName
1 AcctA
2 AcctB
3 AcctC
4 AcctD
5 AcctE
6 AcctF
7 AcctG
8 AcctH

CollateralAccount (Table)
CollID AcctID
1 1
1 2
1 3
1 4
2 1
2 2
2 3
2 4
3 1
3 5
4 6
4 7
5 6
5 7
6 8

Group (Table) - This is what I'm looking to build
GroupID AcctID
1 1
1 2
1 3
1 4
1 5
2 6
2 7
3 8

Appreciate any suggestions / techniques..... Joe

 

[simian336]

Still not sure I understand how you derive you Group table. Could you reexplain your example.

Thanks

Simi

 

[JoeZim]

Sure. For instance, in the resulting Group Table, there is a GroupID = 1 with 5 members (5 AcctIDs are linked to GroupID 1). IF you look at the CollateralAccount Table (I've put them in red text below):

CollateralAccount (Table)
CollID AcctID

1 1
1 2
1 3
1 4
2 1
2 2
2 3
2 4
3 1
3 5


4 6
4 7
5 6
5 7
6 8

Each of these AcctIDs (1-5) share, in one way or another, a CollID. For CollIDs 1 & 2, it's pretty straight forward in that both of these CollIDs are linked to AcctIDs 1 through 4.

Then, when looking at CollID #3, it is linked to only AcctIDs #1 & #5. Since AcctID #1 is already a member of GroupID #1, and AcctID #5 is related to AcctID #1 (they share CollID #3), AcctID #5 would then be added to GroupID #1, which is reflected in the final Group table.

I hope this helps explain what I'm looking for. This seemed like it would be easy at first, but I just keep hitting walls as I dig further. But at the end of the day, I need to setup a bunch of "Groups" of accounts that have any collateral in common. And, each AcctID can be a member of only one group.

Joe