Building a report from a junction table.

[jjmclell]

I'm creating a database in MS Access that has 3 many-to-many table relationships in it. The database is for organizational projects - each project can have many partners involved and can meet the intent of multiple policies, and every combination of this relationship. I was thinking of creating a large junction table to handle all the possible combinations, but was then thinking about how queries would be shown in reports. I think the junction table would look like this:

Now, if I wanted to create a report that was meant to query out a particular project and show all of the policies it relates to and the partners involved, is there a way I can avoid the duplication shown in the following example, and just have 'Marketing review' shown once, with each related policy and partner shown once?:

This is the underlying report query:

Thanks,

Jason


-jjmclell

 

[LaurieHamlin]

I hope your pics are just for demo and you don't actually have fields named Field1. A crosstab query would probably be the easiest way to do it.

 

[MajP]

The sorting and Grouping feature would do that. Make a group on Project ID and you could then also group on Policy. So you could get a report more like

Project: Marketing Review
Policy: 1.2
Partners
Sara
John
Policy: 1.4
Partners
Sara
John

Project: Some Other Project
Policy: x.x

 

[jjmclell]

Alright, I figured out how to do it using sub-reports and grouping in the record source queries. And yes, the above diagrams were for demo purposes only.

Thanks.

-jjmclell