I took some SQL and database in college, but I do not use it much. I am however in charge of running a shooting match for my state (military) and developed an Access database to handle all of the data. Everything works fine except when I try to query the Team Championship.
There are only two tables, Teams and Competitors. This query is supposed to combine the scores from one team match and the competitors scores who all share the same TeamID(Primary Key). The issue is that when I input the score for the Team match, it multiplies the score by the number of team members (4). I'm sure the fix is pretty easy, but I need some fresh and better trained eyes to help me find it. Appreciate all the help in advance.
SELECT Teams.TeamName, Teams.Unit, Sum(Teams.RT3195Score + Competitors.RIRT302Score+[Competitors.RIRT3251Score]+Competitors.PIPT221Score+Competitors.PIPT225Score) AS TeamTotal, Sum(Teams.RT3195Vs + [Competitors.RIRT3251Vs]+Competitors.PIPT221Xs) AS Vs
FROM Teams
INNER JOIN Competitors ON Teams.TeamID = Competitors.TeamID
GROUP BY Teams.TeamName, Teams.Unit
ORDER BY Sum(Teams.RT3195Score + Competitors.RIRT302Score+[Competitors.RIRT3251Score]+Competitors.PIPT221Score+Competitors.PIPT225Score) DESC , Sum(Teams.RT3195Vs + [Competitors.RIRT3251Vs]+Competitors.PIPT221Xs) DESC;