Which sub is the better choice?

How do I create sub-form in Access with selections based on previous choice?

• I have 3 tables: Year, Team, Player. I have a DB of Coaches. What I am trying to achieve is a sub form that is attached to a coach id. I'm ok so far, but here's where my logic gets fuzzy : in the subform I wish to be able to choose a year the coach worked for a championship team, then to show only the teams for the year selected, then to show only the players for that team in that year. So I have a year going back say 50 years and the teams that played in each championship each year and the players that played for the teams in those years. So if I select 1984, when I go to select a team it only provides a choice from teams available for 1984 and then when I select a player it only shows the players for that team in 1984. I have a feeling that I need to create a few queries to obtain the result but would dearly like a logical kick in the head towards a solution. My ERD looks like like a magic trick at the moment ... Thanks in advance for any direction.

• Answer:

  I have something similar working in my database. I have two forms the primary and a subform. the primary form contains details of a customer and the subform contains the notes associated with that particular customer. If you have the subform inside of the primary form, then you would need to do the following. On the properties of the subform, Data tab, your source object would be the table you are pulling from. The Link Child Field/Link Master Field would contain the fields that join the two records together. You can have more then one field joining the records. You could have the Coach/Year on the primary form, then when you select the year, the subform of teams would populate, then by selecting the team you will get the players. hope this helps.