Solved Problem in designing the right query(ies)

[AlexN]

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, everything works fine, I can keep track of team roasters for every period, keep track of matches’ results, can assign points won, can track scorers etc.

Problem appears when it comes to ranking, when there’s a points-tie among two (or more teams). You see, apart from total points won by every team, apart from goals balance, we here have a (rather stupid) rule : when everything else is the same we count points gathered in matches between the two (or more) teams, and then, the goals balance in these specific matches.

I really tried to transform this rule to database logic. Just can’t. Total failure. I mean how do I design a condition of points tie when ranking table (query in this case) is in the form of :

Team	Points
TeamA	10​
TeamB	8​
TeamC	8​
TeamD	8​
TeamE	7​
TeamF	5​
TeamG	5​
TeamH	4​

Issues that I face are:

• How can I compare Points field values to create the condition, them being in the same table?
• How do I isolate the matches between these very teams and then search the matches between them and draw out the appropriate stats?
• Given all above, how can I pass those stats to a table (query) like the one above in order to finally sort correctly?

Note that there will be at least two matches between every pair of teams (Round A, Round B and probably some play-offs).





I know that’s tough and I honestly don’t expect anyone to bother. But I’m addressing to the best in the business and I really have had a lot of help in the past. Any insight will be highly appreciated (again).



Thanks in advance

 

[arnelgp]

can you use VBA function in your query to "tie-break" those ties.
this is but an example, and you need to "enquire" the real "stat" table.

 

[AlexN]

can you use VBA function in your query to "tie-break" those ties.
this is but an example, and you need to "enquire" the real "stat" table.

Thank you arnelgp.
I'll try it and come back.

 

[AlexN]

can you use VBA function in your query to "tie-break" those ties.
this is but an example, and you need to "enquire" the real "stat" table.

Wow!!!
Looks you gave THE solution.
Have to try it more though, to find out what happens if match's result is a draw, but it looks it'll be no problem.
Thank you so much, can't find words to express my gratitude for even bothering!!!

 

[AlexN]

can you use VBA function in your query to "tie-break" those ties.
this is but an example, and you need to "enquire" the real "stat" table.

Well,
It doesn't apply at draws as a match result, which shouldn't be a problem since it's a draw after all, but it takes under consideration all teams' matches (not only the ones' with the tie.) Thus, in the example above, TeamD gets a higher ranking than TeamC and TeamB, although there has been no match like TeamD vs TeamC or TeamD vs TeamB yet (which is wrong).
By any means, the tie-break idea is wonderful and surely shows the way. I hope with some tweaking and improvising I'll manage to get it to work (thanks to you). Plus, it seems I'll have to double the tie-break function since there's a checking of points and of goals as well.
But...(and that's a really big 'but') if you come with any new idea, time given, please post it.

Millions of thanks

 

[arnelgp]

i just "imagined" the match, so i made it up.
actually, on some analysis on post #1, there are only 8 teams.
and those tied also with 8. therefore it might occur that
they indeed get a match on each other.

but i havent seen you match table.

 

[AlexN]

i just "imagined" the match, so i made it up.
actually, on some analysis on post #1, there are only 8 teams.
and those tied also with 8. therefore it might occur that
they indeed get a match on each other.

but i havent seen you match table.

Well actual Teams table is: (names have been changed)

actual matches table is :

and actual rankings table (a query) before applying all criteria needed, is :

You see there are 3 teams with 8 points and 3 others with 4. Their ranking though isn't right because above mentioned criteria haven't been applied (yet)



Thanks

 

[MajP]

can you post the actual tables? I like to see if this can be done in pure SQL.

 

[MajP]

when everything else is the same we count points gathered in matches between the two (or more) teams, and then, the goals balance in these specific matches.

Doing above can probably be done with a few subqueries, but not sure how slow that would be. May be a lot easier to calculate all of this in code and store in a TempTable. It would be easier to demo with the real data.

 

[AlexN]

Doing above can probably be done with a few subqueries, but not sure how slow that would be. May be a lot easier to calculate all of this in code and store in a TempTable. It would be easier to demo with the real data.

Thank you for answering.
Actual data isn't in English so it wouldn't be easily read and understood. I'll modify it a bit and will post basic information needed for the task.
Thanks again

 

[MajP]

Would just need something like the match table in your previous post.

 

[AlexN]

Would just need something like the match table in your previous post.

That's all information needed.
Note that in case of ties in qryTeamStats I must calculate points AND goals ONLY for the matches among the teams involved (if any).
Thank you

 

[MajP]

A: Is ranking based on Points then ties ranked within in the group by points in the group, then point differential in the group
or
B: is ranking based on Points, then total differential for all matchees, and then if there is a tie then rank within the group based on points in the group and then point differential in the group.

It sounds like you are saying A, but normally I think it is done doing B.

 

[MajP]

So this ranks by points then by differential, but not sure if that is what you are saying. I coached soccer so I know you can get very different rules for certain tournaments and sometimes have to read the rules several times.
qryRank1 qryRank1

TeamID	TotalPoints	TotalDifferential	Rank
1​	15​	8​	1​
2​	11​	3​	2​
8​	10​	4​	3​
4​	9​	5​	4​
7​	8​	4​	5​
3​	8​	3​	6​
5​	8​	2​	7​
6​	7​	0​	8​
9​	4​	-2​	9​
14​	4​	-4​	10​
10​	4​	-6​	11​
11​	3​	-4​	12​
13​	2​	-5​	13​
12​	1​	-8​	14​

 

[AlexN]

A: Is ranking based on Points then ties ranked within in the group by points in the group, then point differential in the group
or
B: is ranking based on Points, then total differential for all matchees, and then if there is a tie then rank within the group based on points in the group and then point differential in the group.

It sounds like you are saying A, but normally I think it is done d

Ranking is basically based on total points won.
In case of a tie (two or more teams) the following are taken in consideration (in respective order):

1. Total points won in matches among the tie teams
2. Goal balance (differential) only in matches among the tie teams
3. Goal balance (differential) in all matches
4. Bigger number of goals for (Better Offense) in all matches
5. Smaller number of goals against (Better Defense) in all matches

Hope this helps
Can do 3, 4, 5 , can't do 1 and 2. (that's why the initial post )
Thanks

 

[AlexN]

So this ranks by points then by differential, but not sure if that is what you are saying. I coached soccer so I know you can get very different rules for certain tournaments and sometimes have to read the rules several times.
qryRank1 qryRank1

TeamID	TotalPoints	TotalDifferential	Rank
1​	15​	8​	1​
2​	11​	3​	2​
8​	10​	4​	3​
4​	9​	5​	4​
7​	8​	4​	5​
3​	8​	3​	6​
5​	8​	2​	7​
6​	7​	0​	8​
9​	4​	-2​	9​
14​	4​	-4​	10​
10​	4​	-6​	11​
11​	3​	-4​	12​
13​	2​	-5​	13​
12​	1​	-8​	14​

this actually isn't right with all criteria taken under consideration.
TeamID 3 is 5th. TeamID 7 is 6th, TeamID 5 is 7th
TeamID 9 is 9th, TeamID 14 is 10th, TeamID 10 is 11th right now

 

[MajP]

I could not get this to work in pure Sql so I had to make a temp table.

1. Make a union query

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
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
FROM   tblmatches
ORDER  BY 1,
          3 DESC;

2. Use that to get all stats except points amongst tied group matches and goal balance amongst tie groups matches.

SELECT qrymatchpoints.teamid,
       Sum(qrymatchpoints.matchpoints)       AS TotalPoints,
       Sum(qrymatchpoints.matchdifferential) AS GoalBalance,
       Count(qrymatchpoints.matchid)         AS GamesPlayed,
       Sum(Iif([location] = "home", 1, 0))   AS HomeMatches,
       Sum(Iif([location] = "away", 1, 0))   AS AwayMatches,
       Sum(qrymatchpoints.matchgoalsfor)     AS GoalsFor,
       Sum(qrymatchpoints.matchgoalsagainst) AS GoalsAgainst,
       Sum(qrymatchpoints.matchwin)          AS Wins,
       Sum(qrymatchpoints.matchloss)         AS Losses,
       Sum(qrymatchpoints.matchtie)          AS Ties
FROM   qrymatchpoints
GROUP  BY qrymatchpoints.teamid
ORDER  BY Sum(qrymatchpoints.matchpoints) DESC,
          Sum(qrymatchpoints.matchdifferential) DESC;

3. Append the above to a temp table.

4. Use this code to loop the teams with matching TotalPoints. Use that to build a query to get the points from those matches. This will clear out the old ranking data and append the new data then fill in the data for the tied groups

Public Sub UpdateTempTable()
  'Run Code
  Dim rs As DAO.Recordset
  Dim rs2 As DAO.Recordset
  Dim rs3 As DAO.Recordset
  Dim strSql As String
  Dim Criteria As String
  CurrentDb.Execute "qryDelTempTable"
  CurrentDb.Execute "qryAppendTempTable"
  Set rs = CurrentDb.OpenRecordset("SELECT totalPoints FROM tempTblRankings GROUP BY TotalPoints HAVING Count(tempTblRankings.TeamID)>1")
  Do While Not rs.EOF
    Debug.Print rs!totalPoints
    Set rs2 = CurrentDb.OpenRecordset("Select TeamID from tempTblRankings where TotalPoints = " & rs!totalPoints)
    Criteria = ""
    Do While Not rs2.EOF
       If Criteria = "" Then
        Criteria = rs2!TeamID
      Else
        Criteria = Criteria & ", " & rs2!TeamID
      End If
      rs2.MoveNext
   Loop
     Criteria = "(" & Criteria & ")"
     strSql = "SELECT TeamID, Sum(MatchPoints) AS TotalPoints, Sum(MatchDifferential) AS GoalBalance " & _
            "FROM qryMatchPoints WHERE OpponentID IN " & Criteria & " AND TeamID In " & Criteria & " GROUP BY TeamID"
   Debug.Print strSql
   Set rs3 = CurrentDb.OpenRecordset(strSql)
   Do While Not rs3.EOF
     strSql = "Update tempTblRankings Set TotalPointsTieGroup = " & rs3!totalPoints & ", GoalBalanceTieGroup = " & rs3!GoalBalance & " WHERE TeamID = " & rs3!TeamID
     CurrentDb.Execute strSql
     rs3.MoveNext
   Loop
   rs.MoveNext
  Loop
  rs.Close
  rs2.Close
  rs3.Close
  
  'Update Ranks
  Dim i As Integer
  Set rs = CurrentDb.OpenRecordset("qryRankings")
  Do While Not rs.EOF
    rs.Edit
      i = i + 1
      rs!rank = i
    rs.Update
    rs.MoveNext
  Loop
 End Sub

It appears to work, but the data is not so illustrative
3,5,7 are tied in a group. Only 3 and 5 played each other and they tied. So points in group are 1. Goal balance is zero
9,10,14 are tied in a group, but none of them played each other.

TeamID 3 is 5th. TeamID 7 is 6th, TeamID 5 is 7th

I think 5 is 6th because it has 1 point in the group and 7 has not played in the group.

 

[MajP]

This is the table with the pertinent fields shown.
qryRankings qryRankings

Rank	TeamID	TotalPoints	TotalPointsTieGroup	GoalBalanceTieGroup	GoalBalance	GoalsFor	GoalsAgainst
1​	1​	15​	0​	0​	8​	9​	1​
2​	2​	11​	0​	0​	3​	5​	2​
3​	8​	10​	0​	0​	4​	8​	4​
4​	4​	9​	0​	0​	5​	8​	3​
5​	3​	8​	1​	0​	3​	7​	4​
6​	5​	8​	1​	0​	2​	7​	5​
7​	7​	8​	0​	0​	4​	9​	5​
8​	6​	7​	0​	0​	0​	6​	6​
9​	9​	4​	0​	0​	-2​	4​	6​
10​	14​	4​	0​	0​	-4​	4​	8​
11​	10​	4​	0​	0​	-6​	3​	9​
12​	11​	3​	0​	0​	-4​	1​	5​
13​	13​	2​	0​	0​	-5​	2​	7​
14​	12​	1​	0​	0​	-8​	2​	10​

Only records with matches within a tie (8 points, or 4 Points) are teams 3 and 5 with a tie match.

FYI, I do think someone smart in SQL can do this in pure SQL especially if this was in SQL server. However, that is beyond me.

 

[AlexN]

I could not get this to work in pure Sql so I had to make a temp table.

1. Make a union query

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
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
FROM   tblmatches
ORDER  BY 1,
          3 DESC;

2. Use that to get all stats except points amongst tied group matches and goal balance amongst tie groups matches.

SELECT qrymatchpoints.teamid,
       Sum(qrymatchpoints.matchpoints)       AS TotalPoints,
       Sum(qrymatchpoints.matchdifferential) AS GoalBalance,
       Count(qrymatchpoints.matchid)         AS GamesPlayed,
       Sum(Iif([location] = "home", 1, 0))   AS HomeMatches,
       Sum(Iif([location] = "away", 1, 0))   AS AwayMatches,
       Sum(qrymatchpoints.matchgoalsfor)     AS GoalsFor,
       Sum(qrymatchpoints.matchgoalsagainst) AS GoalsAgainst,
       Sum(qrymatchpoints.matchwin)          AS Wins,
       Sum(qrymatchpoints.matchloss)         AS Losses,
       Sum(qrymatchpoints.matchtie)          AS Ties
FROM   qrymatchpoints
GROUP  BY qrymatchpoints.teamid
ORDER  BY Sum(qrymatchpoints.matchpoints) DESC,
          Sum(qrymatchpoints.matchdifferential) DESC;

3. Append the above to a temp table.

4. Use this code to loop the teams with matching TotalPoints. Use that to build a query to get the points from those matches. This will clear out the old ranking data and append the new data then fill in the data for the tied groups

Public Sub UpdateTempTable()
  'Run Code
  Dim rs As DAO.Recordset
  Dim rs2 As DAO.Recordset
  Dim rs3 As DAO.Recordset
  Dim strSql As String
  Dim Criteria As String
  CurrentDb.Execute "qryDelTempTable"
  CurrentDb.Execute "qryAppendTempTable"
  Set rs = CurrentDb.OpenRecordset("SELECT totalPoints FROM tempTblRankings GROUP BY TotalPoints HAVING Count(tempTblRankings.TeamID)>1")
  Do While Not rs.EOF
    Debug.Print rs!totalPoints
    Set rs2 = CurrentDb.OpenRecordset("Select TeamID from tempTblRankings where TotalPoints = " & rs!totalPoints)
    Criteria = ""
    Do While Not rs2.EOF
       If Criteria = "" Then
        Criteria = rs2!TeamID
      Else
        Criteria = Criteria & ", " & rs2!TeamID
      End If
      rs2.MoveNext
   Loop
     Criteria = "(" & Criteria & ")"
     strSql = "SELECT TeamID, Sum(MatchPoints) AS TotalPoints, Sum(MatchDifferential) AS GoalBalance " & _
            "FROM qryMatchPoints WHERE OpponentID IN " & Criteria & " AND TeamID In " & Criteria & " GROUP BY TeamID"
   Debug.Print strSql
   Set rs3 = CurrentDb.OpenRecordset(strSql)
   Do While Not rs3.EOF
     strSql = "Update tempTblRankings Set TotalPointsTieGroup = " & rs3!totalPoints & ", GoalBalanceTieGroup = " & rs3!GoalBalance & " WHERE TeamID = " & rs3!TeamID
     CurrentDb.Execute strSql
     rs3.MoveNext
   Loop
   rs.MoveNext
  Loop
  rs.Close
  rs2.Close
  rs3.Close

  'Update Ranks
  Dim i As Integer
  Set rs = CurrentDb.OpenRecordset("qryRankings")
  Do While Not rs.EOF
    rs.Edit
      i = i + 1
      rs!rank = i
    rs.Update
    rs.MoveNext
  Loop
End Sub

It appears to work, but the data is not so illustrative
3,5,7 are tied in a group. Only 3 and 5 played each other and they tied. So points in group are 1. Goal balance is zero
9,10,14 are tied in a group, but none of them played each other.


I think 5 is 6th because it has 1 point in the group and 7 has not played in the group.

Ah boy. This is wonderful! Let me implement it and compare results to the official ones.
One question for the time being : Where do I trigger the UpdateTempTable Sub? On Loading the Ranking form maybe? (Never mind, answered when I saw the attached file).


I really can't express my admiration and respect only for being involved, and for your time spent to do all this!
So grateful! Millions of thanks!

 

[MajP]

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​