When all stats are equal (like in the case of TeamID3 and TeamID5), they count total goals differential again along with the one of the matches among them. In that case TeamID3 is 5th, TeamID5 is 6th, and TeamID7 is 7th (as you made it).Here is a small update. I added a column to make the groups stand out.
qryRankings qryRankings
Rank TeamID TotalPoints TieGroup 1 1 15 2 2 11 3 8 10 4 4 9 5 3 8(3, 5, 7) 6 5 8(3, 5, 7) 7 7 8(3, 5, 7) 8 6 7 9 9 4(9, 10, 14) 10 14 4(9, 10, 14) 11 10 4(9, 10, 14) 12 11 3 13 13 2 14 12 1
That's what I was trying to do last night, all night, failing again and againTo view the matches within a Tie Group you can use that column and make a query
....where HomeTeam IN (3,5,7) and AwayTeam IN (3,5,7)
To view the matches within a Tie Group you can use that column and make a query
....where HomeTeam IN (3,5,7) and AwayTeam IN (3,5,7)
PeriodID_FK | Rank | TeamID | TieGroup | TotalPointsTieGroup | GoalBalanceTieGroup |
---|---|---|---|---|---|
1 | | 1 | | |
Yeah, that's the easy way but I'm trying to learn something.nothing like moving the goalposts
Now it becomes too complicated for my poor knowledge. That update would be wonderful, whenever and if you're comfortable .It is not much more complicated. Need to add the Period id to qryMatches, and modify the aggregate query to then group on the Period to make the query "qryTeamStatsByPeriod". Then the update temp table code needs to get modified to handle tie groups by period and ranking by period. Not too complicated, but you are adding one more loop and there are three already so it may be a little confusing to code.
I will try to do an update. Also for the temp table I am not going to add in all of the other statistics fields except these.
tempTblRankings tempTblRankings
These are the only fields needed in the Temp table. The other statistic fields can come directly from the original query they do not need to be copied over. Subtle difference, but this way gives more chance of all the other fields being current because they are calculated dynamic from the queries, even if your application has not yet run the temptable update. If not you may have to run the code to ensure you can see your updated stats. Adding and deleting from a temp table can cause bloating, so you want to limit running the update only when you have new data to change the ranks. (there are other things you can do to limit this. Fixed by compacting and repairing).
PeriodID_FK Rank TeamID TieGroup TotalPointsTieGroup GoalBalanceTieGroup 1 1