I have an application with multi layer relationships. I need to be able to loop though the lower level records that are associate with an upper level table. Example: Team-->Scorecard-->Hole Score. I have a foreign key in the Scorecard table pointing to the Team table and a foreign key in the Hole Score table to the Scorecard but not foreign key in the Hole Score table pointing to the Team table. There are multiple Scorecards for each Team Record and multiple Hole Scores for each Scorecard but I am only interested in doing some accumulations of information in the Hole Score record for the whole team regardless of the scorecard. I prefer to have some nested loops with Loop by Team then Loop through all Hole Score records without having to loop through the Scorecard records. What is the best way to do this? Setup a special relationship or add a foreign key in the Hole Score record back to the Team record. If there is another suggestion I would welcome that as well.
Hope this is clear. If not, I will try to give more detail.