League Table

[Paul Smith]

I am trying to write a query which will produce a league table from the
following table:

MatchID...HomeID...HomeScore...HomeWin...AwayID...AwayScore...AwayWin
1 1 7 N 2
8 Y
2 3 10 Y 4
5 N
3 5 9 Y 6
6 N
4 1 4 N 3
11 Y
5 4 11 Y 5
4 N
6 6 0 N 2
10 Y


PLD....TeamID.....Wins.......Pts
2 3 2 21
2 2 2 18
2 4 1 16
2 5 1 13
2 1 0 11
2 6 0 6

 

[Duane Hookom]

First create a union query to normalize your table "quniScores":
SELECT HomeID as TeamID, HomeScore as PtsF,
AwayScore as PtsA, Abs(HomeScore>AwayScore) As Wins,
Abs(HomeScore<AwayScore) As Losses
FROM tblResults
UNION ALL
SELECT AwayID, AwayScore, HomeScore,
Abs(HomeScore<AwayScore), Abs(HomeScore>AwayScore)
FROM tblResults;

Then create a totals query from the union query:
SELECT quniScores.TeamID, Sum(quniScores.Wins) AS Wins,
Sum(quniScores.Losses) AS Losses, Sum(quniScores.PtsF) AS PtsF,
Sum(quniScores.PtsA) AS PtsA
FROM quniScores
GROUP BY quniScores.TeamID
ORDER BY Sum(quniScores.Wins) DESC , Sum(quniScores.PtsF) DESC;