Students Team Points System

Question

Hi,

I'm trying to create a house team points system for the students in my class.

They are separated in to four different teams; blue, yellow, green, and red.

Simply, it's rather easy to tally the points up for all the students and their associated teams.

However, there are also team challenges that need to be accounted for.

At the moment, I have a students table with all the student info. I have a house team table and a junction table that matches both of these and also has points on it.

e.g.

Student 1 Team 1 4points

Student 2 Team 1 6points

Student 3 Team 2 2 points

I can't for the life of me think of a good way to integrate the following;

Challenge 1 Team 1 6points

Challenge 1 Team 2 3points

So that somewhere in my database I have a formula that counts and displays

Team 1 total points

Team 2 total points

Team 3 total points

I feel like I'm approaching this from one certain angle and would really appreciate a different set of eyes on this and any suggestions for the best way to implement this.

Answer 1

I will elaborate further to illustrate what I mean.

Challenge 1 = Football Tournament

Football Tournament | Team 1 | 5 points

Football Tournament | Team 2 | 2 points

Football Tournament | Team 3 | 7 points

Football Tournament | Team 4 | 3 points

Challenge 2 = Team Quiz

Team Quiz | Team 1 | 2 points

Team Quiz | Team 2 | 3 points

Team Quiz | Team 3 | 4 points

Team Quiz | Team 4 | 6 points

Then, my final form will have;

Team 1

---

Student 1 | 3 points

Student 2 | 7 points

Student 3 | 9 points

Team Quiz | 2 points

Football Tournament | 5 points

Total points = 26 points