Multiple Many-to-Many Report

[willtell]

Hi Everyone,



It's been quite a few years since I've used Access, and I am asking for anyone's opinion on how to solve this dilemma.

I work for a publishing clearing house and I'm trying to set up a simple database for them, I feel like this should be an easy one but for the life of me I can't work it through my brain!

I'll start by describing my tables below, including only important fields:

tblSong - Song_ID (p_key), Song_Name
tblWriter - Writer_ID, Writer_Last, Writer_First
tblPublisher - Publisher_ID, Publisher_Name
tblSongWriter - Song_ID (f_key), Writer_ID (f_key)
tblSongPublisher - Song_ID (f_key), Publisher_ID (f_key)

I want the report to look like the following below:

Line 1: Song_Name, Writer_First, Writer_Last, Publisher_Name
Line 2 (if needed): Writer_First, Writer_Last, Publisher_Name
Line 3 (if needed): Writer_First, Writer_Last, Publisher_Name

Basically, I want to list the song name only once, and then if there are more than one writers, list those, and then the same for publishers...

I've tried but I can't seem to do it without it listing the writers over and over again, for each publisher, if there is more than one, or vice-versa!

Any help would be greatly appreciated!

Thanks,

Brett

[llkhoutx]

Take a look at the attached jpg. I think it gives a better definition of your tables and their relationships.

Note that primary keys are prefixed by PK and foreign keys are prefixed by FK. The mere names tells a lot about the PK/FK fields. Name_ID obscures that information.

Good luck.

[willtell]

Thanks for your reply.

Though, I'm not sure if this definition would work for my database...

I would still need separate tables that uniquely defined a writer and the same for a publisher. Otherwise, I would not be able to differentiate in a query, between two writers (or publishers) of the same name, at least the way I'm understanding it.

For example, if any number of songs were written by any number of different writers, all named John Doe, and I wanted to look up all the songs written by one particular John Doe, I would need a unique identifier that paired that one John Doe to the songs he was a part of. Unless I'm missing something?

I'll keep trying!

Take a look at the attached jpg. I think it gives a better definition of your tables and their relationships.

Note that primary keys are prefixed by PK and foreign keys are prefixed by FK. The mere names tells a lot about the PK/FK fields. Name_ID obscures that information.

Good luck.