Related Tables help
I'm trying to set up a database of music that we sell that will track who contributes to each piece (for compensation purposes).
I have a table called "Music" that contains all our products. Each music record has fields for "Composer", "Arranger 1", "Arranger 2", and "Editor". I have a separate table called "Contributors" that has records for each person that has composed, arranged, or edited any music. One person could arrange a song while another person edits the song, and one person could both compose and arrange the song while someone else edits.
What I want is to be able to select from the list of contributors in each of the fields on the music record and then go to the contributor layout and see all the music that person has either composed, arranged, or edited (and sorted accordingly). I can get it work with one of the fields (the "Editor" field for example) using a join table, but I am having difficulty expanding beyond that.
Can anyone suggest a way to make this work?