Query to find like members and groups that are members of another group

[scor1pion]

Looking for help writing a query that will find the following.
Querying one table and 2 fields
Fields: Group and User ID
The follow member SHIVAK (User ID) is in this Group EDI0090


This group EDIPGM, has both the group EDI0090 and SHIVAK in it




Trying to write a query that will find those type of entries. Where both the Group and a member of the group are both members of another group.

[CJ_London]

this looks like it requires a recursive solution which in access can only be done in vba

it can be done in sql if the depth of the relationships is known. So if the depth is only 2 per your example then join the table to itself on Group=UserID. Perhaps something like this air code

SELECT A.Group, B.UserID
FROM myTable A INNER JOIN myTable B ON A.Group=B.UserID
WHERE A.UserID=B.UserID

[ArviLaanemets]

This looks suspiciously like domain groups and domain users. When this is the case, or when this is something similar, then there may be cases where:
You have Group1 with User1 in it;
Then you have Group2 with Group1 in it;
...
Then you have Group(N) with Group(N-1) in it, and User1 in it, where N may be any number.

So you must find all groups which have User1 directly in it, and then you have to check all such groups for being members of group chain described above, i.e. for every group not Group(N) you have to try to build such chain, and when you can do this, then for this user this group is subgroup of Group(N).

And there is no way you can do this as Access query - Access doesn't support truly recursive queries! The only way to do this will be a VBA procedure.

Maybe there is a way to store results into created empty recordset, and then to return this recordset somehow - but I'm not sure this is possible.

[Welshgasman]

Could it be done in s sub query where the subquery A.ID in (select UserID where Group not in A.Group) ?

[scor1pion]

Thank you all for your replies. As of now I no longer need this query. Turns out the data I was given was inaccurate. That being said, I am also waiting for more data to come in and I may still need to find a way to perform this search. Will cross that bridge if/when I get there.