Easy one (I hope)

[Cantor]

Hi,

There is a sample of my table:

Game # winningTeam losingTeam winScore lostScore

1 Tigers Flames 4 3
2 Canucks Tiger 5 2
3 Pens Canucks 6 2
4 Tigers Av's 2 1



My question is simple. I would like to request the sum of winScore and the sum of lostScore for each team.
I can't group by winningTeam because the score of the winScore when the team is the losingTeam is not in the sum. Clear?
It may seem easy (maybe it is)but I'm a little rusted and with SQL and would like some help.

thanks

 

[GingerR]

since your table structure is not NORMALIZED, this is not simple. you should instead normalize your data....

short of that, do this:

1) if you dont have one already, create a table called TEAMS with a field TeamName. Enter in each team name once only. Make TeamName a Primary Index.

2) Add up scores of Loosing Teams:create a query called LooseScoreSums based on your table described in your question. make it a TOTALS query. Bring down the field 'losingTeam' and leave it as GROUP BY. In the field next to it type

Score: LostScore

and make it SUM instead of GROUP BY.

3) Add up scores of Wining Teams:create a query called WinScoreSums based on your table same as #2. make it a TOTALS query. Bring down the field 'WiningTeam' and leave it as GROUP BY. In the field next to it type

Score: WinScore

and make it SUM instead of GROUP BY.

4) for your final query, create a query and paste this code into the SQL VIEW (go to menu+VIEW+SQL VIEW). then go to VIEW+DESIGN VIEW and see what it did: because every team is listed in your new TEAMS table, it will list EVERY team name, and each of those names if they exist in the WinScoreSums or LooseScoreSums queries.

ok?

g

 

[Cantor]

Sorry for the normalization, I forgot to Preview Post before submit it.
The same sample of my table, normalized:
[tt]
Game # winningTeam losingTeam winScore lostScore

1 Tigers Flames 4 3
2 Canucks Tiger 5 2
3 Pens Canucks 6 2
4 Tigers Av's 2 1
[/tt]

Thanx GingerR for the exhaustive response. The first queries work well and I know I'm on the right way. However, for the final query (point 4) did you forget to put some code? Because I really don't know how to put results of my queries together.

Thanks

Cantor

 

[GingerR]

oops

SELECT Teams.TeamName, WinScoreSums.Score AS WinScoreSum, LooseScoreSums.Score AS LooseScoreSum
FROM (Teams LEFT JOIN LooseScoreSums ON Teams.TeamName = LooseScoreSums.Loser) LEFT JOIN WinScoreSums ON Teams.TeamName = WinScoreSums.Winner;