MajP
You've got your good things, and you've got mine.
- Local time
- Today, 10:30
- Joined
- May 21, 2018
- Messages
- 9,951
This problem relates to this Thread
I have come across something I have never seen, and do not understand.
The OP has a table of soccer matches with Home Team ID, Away Team ID, Home Team Score, Away Team Score as seperate columns. In order to get the stats I normalize this with a union query and works fine.
So now there is a seperate record for each team's match data.
Using an aggregate query I can then sum or count the values to get the statistics.
This is where it gets strange.
If I do the following it works.
qryTotalPointsByPeriod qryTotalPointsByPeriod
If I remove the Count of Matches Played by a team field:
I get the wrong values for TotalPoints in some records even though the group by has not changed.
qryTotalPointsByPeriod_problem qryTotalPointsByPeriod_problem
The Total Points do not match and are wrong. Ex Period 1 team 1 went from 15 to 12 and Period 1 team 2 went from 11 to 8. That difference is one win (3 points) not included in the summation. Anyone seen anything like this?
Now this is the symptom, but it is a bigger problem. There is an issue trying to do any query based on the aggregate.
Any thoughts would be appreciated.
FYI this is not some type of corruption. This can be replicated in a clean database.
Solved - Problem in designing the right query(ies)
Hi everyone again, Ok that’s a tough one. I’m building a soccer league database for my local league. There are tblTeams, tblPlayers, tblRoasters, tblPeriods, tblRounds, tblMatches, and tables tblHomeGoals, tblAwayGoals for keeping track of every match’s goal scorers. The way it is...
www.access-programmers.co.uk
I have come across something I have never seen, and do not understand.
The OP has a table of soccer matches with Home Team ID, Away Team ID, Home Team Score, Away Team Score as seperate columns. In order to get the stats I normalize this with a union query and works fine.
Code:
SELECT tblmatches.matchid,
tblmatches.hometeamid
AS TeamID,
awayteamid
AS OpponentID,
tblmatches.homescore
AS Score,
Iif([homescore] > [awayscore], 3, Iif([homescore] = [awayscore], 1, 0))
AS
MatchPoints,
[homescore] - [awayscore]
AS MatchDifferential,
"home"
AS Location,
homescore
AS MatchGoalsFor,
awayscore
AS MatchGoalsAgainst,
Iif([homescore] > [awayscore], 1, 0)
AS MatchWin,
Iif([homescore] < [awayscore], 1, 0)
AS MatchLoss,
Iif([homescore] = [awayscore], 1, 0)
AS MatchTie,
periodid
FROM tblmatches
UNION
SELECT tblmatches.matchid,
tblmatches.awayteamid
AS TeamID,
hometeamid
AS OpponentID,
tblmatches.awayscore
AS Score,
Iif([homescore] < [awayscore], 3, Iif([homescore] = [awayscore], 1, 0))
AS
MatchPoints,
[awayscore] - [homescore]
AS MatchDifferential,
"away"
AS Location,
awayscore,
homescore,
Iif([homescore] < [awayscore], 1, 0),
Iif([homescore] > [awayscore], 1, 0)
AS MatchLoss,
Iif([homescore] = [awayscore], 1, 0)
AS MatchTie,
periodid
FROM tblmatches
ORDER BY 1,
3 DESC;So now there is a seperate record for each team's match data.
Using an aggregate query I can then sum or count the values to get the statistics.
This is where it gets strange.
If I do the following it works.
Code:
SELECT qrymatchpoints.periodid,
qrymatchpoints.teamid,
Sum(qrymatchpoints.matchpoints) AS TotalPoints,
Count(qrymatchpoints.matchid) AS GamesPlayed
FROM qrymatchpoints
GROUP BY qrymatchpoints.periodid,
qrymatchpoints.teamid
ORDER BY qrymatchpoints.periodid,
Sum(qrymatchpoints.matchpoints) DESC;| PeriodID | TeamID | TotalPoints | GamesPlayed |
|---|---|---|---|
1 | 1 | 15 | 5 |
1 | 2 | 11 | 5 |
1 | 8 | 10 | 5 |
1 | 4 | 9 | 5 |
1 | 3 | 8 | 5 |
1 | 5 | 8 | 5 |
1 | 7 | 8 | 5 |
1 | 6 | 7 | 5 |
1 | 14 | 4 | 5 |
1 | 9 | 4 | 5 |
1 | 10 | 4 | 5 |
1 | 11 | 3 | 5 |
1 | 13 | 2 | 5 |
1 | 12 | 1 | 5 |
2 | 12 | 13 | 5 |
2 | 13 | 11 | 5 |
2 | 14 | 10 | 5 |
2 | 10 | 10 | 5 |
2 | 11 | 9 | 5 |
2 | 6 | 7 | 5 |
2 | 9 | 7 | 5 |
2 | 4 | 6 | 5 |
2 | 3 | 5 | 5 |
2 | 5 | 5 | 5 |
2 | 7 | 5 | 5 |
2 | 8 | 4 | 5 |
2 | 2 | 2 | 5 |
2 | 1 | 0 | 5 |
If I remove the Count of Matches Played by a team field:
Code:
Count(qrymatchpoints.matchid) AS GamesPlayed
Code:
SELECT qrymatchpoints.periodid,
qrymatchpoints.teamid,
Sum(qrymatchpoints.matchpoints) AS TotalPoints
FROM qrymatchpoints
GROUP BY qrymatchpoints.periodid,
qrymatchpoints.teamid
ORDER BY qrymatchpoints.periodid,
Sum(qrymatchpoints.matchpoints) DESC;I get the wrong values for TotalPoints in some records even though the group by has not changed.
qryTotalPointsByPeriod_problem qryTotalPointsByPeriod_problem
| PeriodID | TeamID | TotalPoints |
|---|---|---|
1 | 1 | 12 |
1 | 8 | 10 |
1 | 4 | 9 |
1 | 2 | 8 |
1 | 3 | 8 |
1 | 5 | 8 |
1 | 7 | 8 |
1 | 6 | 7 |
1 | 14 | 4 |
1 | 9 | 4 |
1 | 10 | 4 |
1 | 11 | 3 |
1 | 13 | 2 |
1 | 12 | 1 |
2 | 12 | 13 |
2 | 13 | 11 |
2 | 14 | 10 |
2 | 11 | 9 |
2 | 6 | 7 |
2 | 9 | 7 |
2 | 10 | 7 |
2 | 4 | 6 |
2 | 3 | 5 |
2 | 5 | 5 |
2 | 7 | 5 |
2 | 8 | 4 |
2 | 2 | 2 |
2 | 1 | 0 |
The Total Points do not match and are wrong. Ex Period 1 team 1 went from 15 to 12 and Period 1 team 2 went from 11 to 8. That difference is one win (3 points) not included in the summation. Anyone seen anything like this?
Now this is the symptom, but it is a bigger problem. There is an issue trying to do any query based on the aggregate.
Any thoughts would be appreciated.
FYI this is not some type of corruption. This can be replicated in a clean database.
Last edited:
