Displaying Many to Many Relationships (1 Viewer)

Hi All

I am teaching myself database design and Access from the web. Its fun and I have made some good progress! But, just before the finish line I got stuck.

My problem is displaying data in many-to-many relationships.

There are three main tables: tblShops, tblOwners and tblMarketingEvents. These are linked through two junction tables creating two many-to-many relationships:
1) Each Shop may have more than one Owner and each Owner may have more than one Shop.
2) Each Marketing Event may reach more than one Owner and each Owner may be reached by more than one Event.

Now I want to display for each Marketing Event:
1) the data of that event, (tblMarketingEvents)
2) plus the owners reached in that event, (tblOwners)
3) plus all the shops owned by the owners reached in that event. (tblShops)

Preferably, I want to display more than one record at a time without repeating any information.

Is there a way to display the data like this?

I tried a query: it gives the right data but does not display it as I need. It repeats the data from tblMarketingEvents and tblOwners for each shop.
I tried a form with subform: it can only display one record at a time and is hard to work with (printing etc)
I tried a report with grouping levels based on the query: Again, right information, wrong display. I need to group according to the records of the MarketingEvents table and not just according to a single field.

Any help will be appreciated!
Thanks.

(I wanted to post screenshots and examples to make it easier to follow but this site does not allow me.:banghead

because of the relationships between Marketing Events/Owners/Shops being :
Many to Many to Many, you can't display all the data without repeating data.
You could have your Events form and use a sub form to display each owner (using single form display and navigation buttons, not continuous) and have a sub form on this Owners form showing all shops for that Owner using a continuous or datasheet.
As for a report, you'll need to have 2 grouping levels, to get down to single line data

David

Thanks a lot, David.

It seems I tried to do something that is impossible so no wonder I could not get any results or info on the web. I am a little disappointed. I really need that.

Maybe I will have to go back to using a word document for this.

Here is a free video describing many to many.

Good luck.