Top 3 of multible catorgies

[Ed Jordan]

SELECT Patrols.Level, Events.[Event Name], Patrols.[Patrol Name],
Patrols.Troop, [Score]![Score]*0.75+[Score]![Sprit]*0.25 AS Expr1
FROM Troops INNER JOIN (Patrols INNER JOIN (Events INNER JOIN Score ON
Events.[Event ID] = Score.[Event ID]) ON Patrols.ID = Score.[Patrol Name]) ON
Troops.ID = Patrols.Troop
ORDER BY Patrols.Level DESC , Events.[Event Name] DESC ,
[Score]![Score]*0.75+[Score]![Sprit]*0.25 DESC;

how do i make it so it will only display the top 3 scores from
([Score]![Score]*0.75+[Score]![Sprit]*0.25 AS Expr1) expression, in each of
the events, in the 3 different levels.

eg.
Upper level
event 1
top score 1
top score 2
top score 3
event 2
top score 1
top score 2
top score 3
Lower level
event 1
top score 1
top score 2
top score 3
event 2
top score 1
top score 2
top score 3


there is 3 different " Patrols.Level"
and 12 different "Events.[Event Name]"

thanks ed

 

[Ed Jordan]

eg.
Upper level event 1 top score 1 Patrols.[Patrol Name] Patrols.Troop
Upper level event 1 top score 2 Patrols.[Patrol Name] Patrols.Troop
Upper level event 1 top score 3 Patrols.[Patrol Name] Patrols.Troop
Upper level event 2 top score 1 Patrols.[Patrol Name] Patrols.Troop
Upper level event 2 top score 2 Patrols.[Patrol Name] Patrols.Troop
Upper level event 2 top score 3 Patrols.[Patrol Name] Patrols.Troop
Lower level event 1 top score 1 Patrols.[Patrol Name] Patrols.Troop
Lower level event 1 top score 2 Patrols.[Patrol Name] Patrols.Troop
Lower level event 1 top score 3 Patrols.[Patrol Name] Patrols.Troop
Lower level event 2 top score 1 Patrols.[Patrol Name] Patrols.Troop
Lower level event 2 top score 2 Patrols.[Patrol Name] Patrols.Troop
Lower level event 2 top score 3 Patrols.[Patrol Name] Patrols.Troop

that is more what it should look like

 

[Michel Walsh]

You can either rank by group, either use a subquery like:


SELECT country, city
FROM tableName AS a
WHERE city IN ( SELECT TOP 3 b.city
FROM tableName AS b
WHERE b.country=a.country
ORDER BY b.population DESC )
ORDER BY country, population DESC




A ranking technique could be like:


SELECT a.country, a.city
FROM tableName As a INNER JOIN tableName AS b
ON a.country=b.country AND a.population <= b.population
GROUP BY a.country, a.city
HAVING COUNT(*) <= 3
ORDER BY a.country, COUNT(*)



These two queries should give the top 3 cities, per country (the cities
being ranked by their population). Their last ORDER BY is facultative.
You can use a saved query instead of a 'tableName'.



Hoping it may help,
Vanderghast, Access MVP