TOP function - Return only complete groups

[mvilela]

I have a query to group team athletes.


SELECT Consulta_Jovens.ID, Consulta_Jovens.cla_escalao, Consulta_Jovens.cla_geral, Consulta_Jovens.voltas, Consulta_Jovens.chegada, Consulta_Jovens.tempo, Consulta_Jovens.t_liquido, Consulta_Jovens.dorsal, Consulta_Jovens.equipa, Consulta_Jovens.nome, Consulta_Jovens.cidade, Consulta_Jovens.escalao, Consulta_Jovens.cla, Consulta_Jovens.pontos
FROM Consulta_Jovens
WHERE (((Consulta_Jovens.dorsal) In (SELECT TOP 3 dorsal FROM Consulta_Jovens AS TT WHERE TT.equipa=Consulta_Jovens.equipa ORDER BY TT.pontos desc, TT.ID desc)));




This query returns all participating teams.
I intended to only return the complete teams (in this case 3 athletes).

[June7]

Are you saying a team is only 3 athletes?

It should be returning the TOP 3 dorsal from each equipa because of the correlated subquery WHERE clause. If you want something different, please clarify.

Could help to provide sample data - raw and expected output. Can build tables in post (Go Advanced edit toolbar or copy/paste Access records or Excel cells) or attach file (follow instructions at bottom of my post).

[mvilela]

Are you saying a team is only 3 athletes?

It should be returning the TOP 3 dorsal from each equipa because of the correlated subquery WHERE clause. If you want something different, please clarify.

Could help to provide sample data - raw and expected output. Can build tables in post (Go Advanced edit toolbar or copy/paste Access records or Excel cells) or attach file (follow instructions at bottom of my post).

All athletes belong to a team. The best 3 teams count towards the team ranking, provided that they have 3 athletes who have completed the race.
The general table is imported directly from a CSV file.
I am a beginner in Access.
The database is attached.

[June7]

The best 3 teams count towards the team ranking

Did you mean the "The best 3 athletes"?

Why are ID and cla_geral the same data? Why have both fields?

There can be 1 to 18 athletes per team?

If you want query to pull all VET records, could do escalao LIKE "VET*"

Still not clear to me what the issue is. The query is returning top 3 athletes per team. If team has less than 3 athletes, it returns what they have. Do you want to not include those teams? That will complicate query as this will require in aggregate query that counts how many athletes in each team.

[mvilela]

Did you mean the "The best 3 athletes"?

Why are ID and cla_geral the same data? Why have both fields?

There can be 1 to 18 athletes per team?

If you want query to pull all VET records, could do escalao LIKE "VET*"

Still not clear to me what the issue is. The query is returning top 3 athletes per team. If team has less than 3 athletes, it returns what they have. Do you want to not include those teams? That will complicate query as this will require in aggregate query that counts how many athletes in each team.

There are 2 team classifications; "Jovens" and "Adultos".
Adultos: "Elites" Or "VET_30" Or "VET_35" Or "VET_40" Or "VET_45" Or "VET_50" Or "VET_55" Or "VET_60" Or "Juniors" Or "feminine" - Consulta_Adultos_TOP3
Jovens: "Benjamin_F" Or "Benjamin_M" Or "Infantil_F" Or "Infantil_M" Or "Iniciado_M" Or "Iniciado_F" Or "Juvenil_M" Or "Juvenil_F" Or "Cadete_F" Or "Cadete_M" - Consulta_jovens_TOP3


First, the classification by echelon "cla_escalao" is drawn up and points are awarded to the athletes according to the "pontos" table


Then, the classification by teams is drawn up according to the groupings indicated above, where only the teams count. that have at least 3 athletes. The points of the 3 best athletes of each team count.


I want a query that sorts first by teams with 3 athletes, and then by team score.

[June7]

As I said, somewhere you need do an aggregation of data that counts how many athletes in each team then restrict records by that.

SELECT equipa, Count(nome) AS CountAthlete FROM geral GROUP BY equipa;

Then include that query in Consulta_Adultos joining on equipa fields, add filter criteria: CountAthlete >= 3

Or use DCount() domain aggregate function, however, performance will likely be slower.

[mvilela]

As I said, somewhere you need do an aggregation of data that counts how many athletes in each team then restrict records by that.

SELECT equipa, Count(nome) AS CountAthlete FROM geral GROUP BY equipa;

Then include that query in Consulta_Adultos joining on equipa fields, add filter criteria: CountAthlete >= 3

Or use DCount() domain aggregate function, however, performance will likely be slower.

Thank you for your help, which was excellent in resolving the issue.