INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Building a report from a junction table.

Building a report from a junction table.

(OP)
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

RE: Building a report from a junction table.

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.

RE: Building a report from a junction table.

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

RE: Building a report from a junction table.

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close