5 Replies Latest reply on Mar 19, 2015 4:30 PM by philmodjunk

    How to associate, Age Division, Team Name and Player

    SkipGilleland

      Title

      How to associate, Age Division, Team Name and Player

      Post

      I need to find a way via relationships, or somehow to do the following:

      I have a table "Age Division" (11U, 12U, 13U, etc..)

      Another table called "Team Name" (Red, White, Blue, etc)

      I need to join the "Age Division" and "Team Name" to get something like the following.  11U Red, 11U White, 12U Red, 12U White, etc..

      By adding Age Division and Team Name, I will have the team (12 Red)...then I need a way to associate a player to that team.

      I am not understanding what I need to do...any help would be appreciated

        • 1. Re: How to associate, Age Division, Team Name and Player
          philmodjunk

          Are all combinations of age division and team possible? So if you have four divisions and four teams, you'd have 16 possible combinations?

          • 2. Re: How to associate, Age Division, Team Name and Player
            SkipGilleland

            That is correct....11U may have the same team names as 12U (11U Red, 12U Red), but one division could have completely different names...for example 18U Elite.

             

             

            • 3. Re: How to associate, Age Division, Team Name and Player
              philmodjunk

              You would appear to need these tables and relationships:

              Players------<Roster>--------Team
                                        v
                                         |
                                   age Division

              Players::__pkPlayerID = Roster::_fkPlayerID
              Team::__pkTeamID = Roster::_fkTeamID
              Age Division::__pkAgeDivID = Roster_fkAgeDivID

              For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              • 4. Re: How to associate, Age Division, Team Name and Player
                SkipGilleland

                Ok, I have that setup...now I have a player layout and I want to be able to choose a team for a player to be on.  I want Jenny Jumper to be on 11U Elite.  How do I get the 11U (Age Division) and Elite (Team) to be concat'd so I am able to choose the whole thing verses having to choose the Age division from one field and the team from another field?

                • 5. Re: How to associate, Age Division, Team Name and Player
                  philmodjunk

                  I would use separate fields with separate drop down lists myself. The two lists will be many times shorter than a combined list. Remember my earlier question where 4 teams with 4 age categories produces a list of 16 different combinations. If you had as few as 10 teams and 10 age divisions, that makes for a list of 100 combinations and your list becomes fairly unwieldy.

                  Please note that after you have selected both a team and a division, a calculation field can display the two selections as a single text string in a single field for reporting purposes.

                  And after posting my original comment, it occurs to me that you might not actually need a Rosters table. That table makes it possible to assign one player to more than one team. That could either be a case of allowing a player to compete on more than one team in a single season or assigning a player to a team for last year's season and then to a different team for this year's season while keeping past and present assignments in the same table.

                  If you don't see any possible need for either scenario, you can simplify your design by linking the players table directly to the team and age division tables