Attached is a sample DB (Test_SDP.zip) with all tables, queries, reports, and screen shots mentioned here. It contains 2 tables (Test_Owners and Test_Members) with 4 fields in each table. The fields are ID, User, Group, and Share.
Share represents file shares on a network server in both tables.
Group represents the group that has access to the share in both tables.
User represents owners of the group in the Test_Owners table and members of the group in the Test_Members table.
A share can have multiple groups associated to it. A group can have multiple owners and multiple members. A group owner can also be listed as a member.
I am trying to combine shares, groups, owners, and members into 1 report that would look like this screen shot.
There are several reports in the sample DB. If you run the reports just enter an asterisk "*" at the "Path" prompt to view the report.
The "Test Group Members by Share" report will show the shares, groups, and members.
The "Test Group Owners by Share" report will show the shares, groups, and owners.
The problem is when I try to combine the 2 previous reports the members are duplicated for each owner as seen in the next 2 screen shots.
Here is a screen shot of the "Test Group M&O by Share" report:
Here is a screen shot of the "Test Group M&O Sub by Share" report:
I think the problem is with the many to many relationships but I do not know how to correct it. I have read about needing a junction table but I have no experience with this. If someone could explain or modify the attached sample db and reply with the fixed db I would be very thankful.
Thanks,
John