Dave said:
I created a database for an archery tournament and I would like to assign
people to a team and have the scores add together automatically for a
combined total. My problem is some people may be on more than one team and
their individual score needs to be used on multiple teams. Is there a way
to
do this? I would need to be able to assign more than one team number to a
person.
So the same score (for a particular person) may be tallied to multiple
teams? That seems odd, but if that's the way it works ...
You're going to need at least the following tables:
+ Teams -- one record for each team
+ Archers (or People) -- one record for each person particpating
+ TeamMembers -- one record for each member of a team. Fields in this table
will include the TeamID or TeamName (whichever it the primary key of the
Teams table) and the ArcherID or ArcherName (whichever is the primary key of
the Archers table). So you see, the presence of a record in this table
indicates that *this* person is on *this* team.
+ Scores -- one record for each score. This table would have a ScoreID
field, probably an autonumber, as its primary key, and an ArcherID (or
ArcherName) linking it to the archer who got the score, as well as the score
value itself and anything else that is relevant to the score.
To get the current cumulative score for a team, you'd use a totals query
along these lines:
SELECT TeamID, TeamName, Sum(Score) As TotalScore
FROM
Teams
INNER JOIN TeamMembers
ON Teams.TeamID = TeamMembers.TeamID
INNER JOIN Scores
ON TeamMembers.ArcherID = Scores.ArcherID
GROUP BY TeamID, TeamName;
That's "air SQL", and your actual table and field names may of course be
different, but it would be something along those lines.
