Are all combinations of age division and team possible? So if you have four divisions and four teams, you'd have 16 possible combinations?
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.
You would appear to need these tables and relationships:
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
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?
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