6 Replies Latest reply on Sep 22, 2010 11:55 AM by Sunny1

    question on multiple occurances

    Sunny1

      Title

      question on multiple occurances

      Post

      Hello,

      If I have a person in my database who is a member of more than one committee, how do I represent that?  Example, I have three different committee names and this person is a member of all three.  If I have a tab name Committees on each employee record, and a field for Committee on that tab, how do I have them assigned to each committee so that if I print a report, they appear in each committee on the report?  Do I set up a Committee Table and have it related to the main table?

      Thank you in advance.

        • 1. Re: question on multiple occurances
          philmodjunk

          You'll likely need two more tables, one with one record for each committee and one to list committee membership.

          Personnel---<CommitteeMembership>----Committees      ( ---< means 1 to many )

          Personnel::PersonnelID = CommitteeMembership::PersonnelID
          Committee::CommitteeID = CommitteeMembership::CommitteeID

          To list committee membership for one, a selected group, or all committees, you'd put together a summary report based on the CommitteeMembership table but include fields from both Personnel and Committees to fill in all the needed info.

          This is a typical many to many relationship as many people can be a member of many committees.

          Here's a demo file of a many to many relationship. It relates contracts to companies but it's the same concept:

          http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

          • 2. Re: question on multiple occurances
            Sunny1

            Could you help me understand how I download this demo.  I had a problem with doing this before.  I went to the area, clicked download now but I can't see where I can find it. 

            • 3. Re: question on multiple occurances
              philmodjunk

              There's a blue download button. Click it. Wait the 20 seconds while the system tries to sell you an upgrade for the download link to appear. Then click the download now text to download the file.

              • 4. Re: question on multiple occurances
                Sunny1

                I have the two additional tables.  I have entered the first committee each member is in.  Now, when I go to enter the second committee a staff member is in, it treats it like I am creating a new Personnel member rather than a new entry for existing personnel.  Tying PersonnelID and CommitteeID together in the relationships I think is the problem. I want to be able to pick the person's name in the field for Name on the Membership entry form.  what am I doing wrong?

                • 5. Re: question on multiple occurances
                  philmodjunk

                  The demo file shows how to handle this.

                  1. The portal should be based on the Join table, CommitteeMembership
                  2. The relationships allow creation of records via the relationship for the join table from both Personnel and Committees
                  3. In the portal on the Personnel layout, add the field for CommitteeID and format it as a pop-up menu with a 2 column value list. Column 1 is the CommitteeID number from the committees table. Column 2 is the committee name from this same table. Hide column 1. The value list will show committee names, but enter committee ID numbers to link the selected committee to the current personnel record.
                  • 6. Re: question on multiple occurances
                    Sunny1

                    Thank you.  I hadn't thought about doing it as a portal.  That worked.

                    Always the hero!