5 Replies Latest reply on Jun 11, 2015 7:40 PM by AaronFerguson_1

    Related Tables help

    AaronFerguson_1

      Title

      Related Tables help

      Post

      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?

      Thank you

        • 1. Re: Related Tables help
          philmodjunk

          I don't see any related tables except in your description and the lack of related tables is a major part of the problem here. You have a many to many relationship and need a join table between the music and a contributor table that documents each contributor's contribution to a given record in the music table.

          Music-----<Music_Contributor>-----Contributors

          Music::__pkMusicID = Music_Contributor::_fkMusicID
          Contributors::__pkContributorID = Music_Contributor::_fkContributorID

          You can place a portal to Music_Contributor on the Music layout to list and select  Contributors records for each given Music record. Fields from Contributors can be included in the Portal to show additional info about each selected Contributors record and the _fkContributorID field can be set up with a value list for selecting Contributors records by their ID field. A field in the Music_Contributor field can be set up with a value list to identify the type of contribution as: "composer"; "arranger"; "editor"; etc.

          • 2. Re: Related Tables help
            AaronFerguson_1

            Sorry I wasn't clear. I did have a join table and was relating it as you said. The only problem is I can't make the same person do more than one thing on one song. For example, I can't have John Doe be both the Arranger and Editor on "Twinkle Little Star." In the portal I set up on John Doe's record, it only show as one or the other - not both.

            • 3. Re: Related Tables help
              philmodjunk

              Of course you can. Just create two join table records linked to the same person  and music record but with a different value selected in the ContributionType field.

              • 4. Re: Related Tables help
                AaronFerguson_1

                I figured out that it is creating two records in the join table; however, the portal on the Contributor layout will only show whichever record was created first.

                Here is a link to my file. 

                • 5. Re: Related Tables help
                  AaronFerguson_1

                  I got it to work. My problem was I made my portal to the Music table rather than the join table.

                  Thanks for the help.