Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Building a report from a junction table.

Status
Not open for further replies.

jjmclell

Technical User
Aug 10, 2007
34
CA
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:

Capture1_uopvis.jpg


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?:

Capture2_gfdkf5.jpg


This is the underlying report query:

Capture3_jzitb3.jpg


Thanks,

Jason


-jjmclell
 
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.
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top