Active Directory Group and User Export - How do I display nesting for a user?

[drex]

Hello.

I am a MS Access noob here and would like some feedback on my dilemma. I have nightly jobs which export Active Directory groups and their direct members to a csv file. I also have a similar job which exports user information except group membership. It is impossible for me to export all direct and indirect (nested) group membership to a csv file as our environment contains 8200 groups and 7400 users. I ended up exceeding character limits Essentially, I would like to build a query which shows the user and all groups they are members of (both direct and indirect). It would be a bonus to show where the user obtained its membership as well. Let me try to explain below.


Here is the format of the data imported into Access.. Group names are colors. RED is a group inside the YELLOW group and Sue is a member of RED and BLUE. She is also a member of YELLOW.

ID	Group Name	Member
1	RED	Sue
2	RED	Mike
3	RED	Bob
4	BLUE	Allen
5	BLUE	Joe
6	BLUE	Sue
7	YELLOW	Joe
8	YELLOW	RED

This is what I'd like to display with the query..

	Member	Group Name	Group Name	Group Name	Group Name
	Mike	RED	YELLOW
	Allen	BLUE
	Joe	BLUE	YELLOW
	Bob	RED	YELLOW
	Sue	RED	BLUE	YELLOW

Is this possible?


Thanks for your help!

[June7]

RED is a member?

There 8 records in the raw data but 10 values in the output - why?

Options:

1. http://allenbrowne.com/func-concat.html

2. http://forums.aspfree.com/microsoft-...ry-322123.html

3. have another field that assigns a sequence number to each member's records and build a crosstab query with that sequence number field as the column header, use First aggregate function

[drex]

The reason for 10 values in the output is because of the nesting. RED is and can be a member of other Group Names. I have another table with just the usernames. I can use that as a reference. I am still confused how to achieve this.

RED is a member?

There 8 records in the raw data but 10 values in the output - why?

Options:

1. http://allenbrowne.com/func-concat.html

2. http://forums.aspfree.com/microsoft-...ry-322123.html

3. have another field that assigns a sequence number to each member's records and build a crosstab query with that sequence number field as the column header, use First aggregate function

[June7]

You want to generate a value based on some logical criteria (if member is in RED also include them in YELLOW). A SELECT query alone is not going to accomplish that. Either need an actual record that puts Mike and Sue in group YELLOW, or use VBA code and a temp table to create the desired output.

[drex]

Is there a way to do an "if x contains y, then display y"? I know there is a way to do this in Excel using the vlookup function.

You want to generate a value based on some logical criteria (if member is in RED also include them in YELLOW). A SELECT query alone is not going to accomplish that. Either need an actual record that puts Mike and Sue in group YELLOW, or use VBA code and a temp table to create the desired output.

[June7]

Not sure what you mean. What are x and y - field and its value?

What was not clear about my earlier comments?

[drex]

For example.. X = RED and Y = Blue. X is a value and y is a value on one row. Is this a strange request? I would think others have similar needs.. Hmm..

I have been researching the Dlookup function. I might be able to make it work. Once again, I am very new to Access and I don't program in VB. My knowledge is limited to PowerShell and a couple GUI tools, etc. MS Access is an alien world to me.

[June7]

Okay, I will adjust my earlier statement about options. A DLookup might serve. How many of these 'nestings' must be accounted for?

[drex]

I'm not sure how many nestings we have off the top of my head. I'm guessing it could be up to 5 or 10.

[drex]

Can someone provide input on this?