Solved Problem in designing the right query(ies)

[MajP]

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)

 

[AlexN]

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​

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).

I really believe this thread should be pinned for the cleverness and professionalism of the solution you provided

Thank you so much.

 

[AlexN]

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)

That's what I was trying to do last night, all night, failing again and again

Thank you!

 

[AlexN]

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)

Well, I hate to do this but, I really had to come back.

First of all, the solution you came up with, works like a charm and I honestly find it very smart. I could never think of it. And, thank you, thank you again. It’s not only the solution that you gave but the lesson I got in thinking the way I should.

Problem is, this database is supposed to hold records for several seasons. (The sample I attached has records only for the running season (field PeriodID in tblMatches) ‘cause I haven’t had the time to enter previous season’s matches.

If someone in the future (say 2025) wants to view 2023’s (final) ranking I’m afraid the thing with the tempTable won’t work. Also, somehow, PeriodID has to be a criterion in searching for previous seasons' rankings.

I’m thinking of a (permanent) table for Rankings where data, somehow, would be stored once a season is completed (and information would be called via an action different than the current season’s one).



What’s your opinion?



Thanks

 

[CJ_London]

nothing like moving the goalposts

 

[MajP]

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

PeriodID_FK	Rank	TeamID	TieGroup	TotalPointsTieGroup	GoalBalanceTieGroup
1​	​	1​		​	​

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).

 

[AlexN]

nothing like moving the goalposts

Yeah, that's the easy way but I'm trying to learn something.

 

[AlexN]

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

PeriodID_FK	Rank	TeamID	TieGroup	TotalPointsTieGroup	GoalBalanceTieGroup
1​	​	1​		​	​

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).

Now it becomes too complicated for my poor knowledge. That update would be wonderful, whenever and if you're comfortable .


Thanks again.

 

[MajP]

Here is an update demo. In order to demo this I started building an application, and it slowly grew out of control. Sunk costs. Once you get started it is easy to keep adding. But it is pretty cool

So from the combo pick a Period. In the top tabs you have all matches for that period. In the second you have statistics
If you select any Team record in the stats form it shows all matches for that team in that period below.
If they are in a Tie Group it shows the matches for that Tie Group

You only need to update the rankings after data entry. This could get a little expensive if you force it to fire after each new match is added. You may want to only do it after all updates are made and you then want to see the updated rankings.

 

[MajP]

FYI,
I came across some strange issues that I think are some kind of Access anomaly, but this caused me to store some things in the temp table that are not necessary.

Solved - Aggregate Query Returns Different Values When a Field is Included not in Group By

This problem relates to this Thread https://www.access-programmers.co.uk/forums/threads/problem-in-designing-the-right-query-ies.324849/ 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...

www.access-programmers.co.uk

Hopefully this does not bubble up in other places.

 

[AlexN]

Ah boy! This is too much!
I'm gonna integrate it in my db and see how it goes. I had designed a form for Rankings and Stats but I think I like yours better!
Thank you so much!


P.S. Not so long ago this forum had a rep point system (or am I wrong?) IF EXISTS you got all I can give! Thank you, thank you, thank you!