Many-to-Many Relationship in a Report

[rhewitt]

.Hi!



Let me preface this by saying I'm working with SQL Server 2012, so I don't know of an easy way to share the database with the forum. I'll try to provide as much detail as possible.

I'm trying to show all personnel related to a project. To do this I created a Many-to-Many relationship, because a project can have several people associated with it, and a single person can be associated with multiple projects.

My tables:

• tblProjects: ProjectID, ...
• junctionProjectIndividuals: JunctionID, IndividualID, Category, ProjectID
• tblIndividuals: IndividualsID, FirstName, LastName, ... (this table acts as an address book)

I have a report that brings in all the data related to a single project. I'm trying to show a list of Project Personnel related to the project, but I'm stuck.

Question: How do I create a sub-report of project personnel based on a Many-to-Many relationship.

[Xipooo]

What is the current SQL for your query behind the report?

[rhewitt]

The main report or the subreport?

I tried to make a query that combined junctionProjectIndividuals with tblIndividuals so I would have the ProjectID field, but it came out to +100k records. Rather than having ~3 people associated with a project I had every record in the tblIndividuals table for each project.

[Xipooo]

When you say "subreport" what do you mean exactly?

[rhewitt]

My "Main" report would be the original report based on tblProject". A "Subreport" would be a way to get data from related tables into the "Main" report (based on the ProjectID primary key/foreign key relationship).

I've modeled this in my data entry form (the "Main" form refers to fields in the tblProject table while data that are saved to related tables are in "SubForms"). In this case I'm editing junctionProjectIndividuals as a subform:

The subform above represents the same relationship, but on a form rather than a Report. The "Contact" field here is filled out by tblIndividuals. When you make a selection it stores the IndividualID in the Junction table.

Basically what I'm looking for is to use junctionProjectIndividals as a subreport, but instead of showing the IndividualID, I want to show the contact information from tblIndividuals.

Not sure if that made it more or less clear.

Basically I just want to be able to show all the Contacts from tblIndividuals for each project in my "Main" report. Since it's a many-to-many relationship I don't have a ProjectID in tblIndividuals so it's not as easy as tables with a one-to-many relationship.

[Xipooo]

Ok, in your query behind the subreport, you need to join all three tables together just as they are set up in your relationships. This will make all fields available from all of the forms.

Then you can link your Master Field of the main report and Child Field of the subreport so that the subreport shows the details of the item in the main report.

You may want to consider using grouping to group the details of an item instead of a subreport.

[June7]

Yes, maybe just use report Grouping & Sorting features, instead of a subreport. However, if you want to do statistics on the data (counts and sums) this will not work properly. Other methods would be needed, subreport being one.

[rhewitt]

Ok, in your query behind the subreport, you need to join all three tables together just as they are set up in your relationships. This will make all fields available from all of the forms.

Then you can link your Master Field of the main report and Child Field of the subreport so that the subreport shows the details of the item in the main report.

You may want to consider using grouping to group the details of an item instead of a subreport.

My Solution (thanks Xipooo):
Create a query using all three tables. Design a report based on that query. Add report to main report, link Master/Child fields using ProjectID

Thanks!