Old chestnut, ranking query 1

[TrekBiker]

Sorry to have to ask this as it's a frequent subject but I'm struggling to add ranking by Total for each Company in each Category in this query.

What I want is this, etc

Using other posts I've tried this SQL, amongst others, but am not getting what I want.

SELECT s1.Category, s1.Company, s1.EntryID, s1.Judge, s1.Total, Count(s2.Total)+1 AS Rank
FROM qryPoints AS s1 INNER JOIN qryPoints AS s2 ON s1.EntryID = s2.EntryID
GROUP BY s1.Category, s1.Company, s1.EntryID, s1.Judge, s1.Total;

It just counts the number of Judges for each Company and adds 1.

 

[dhookom]

I would use a subquery like:

SELECT qselTrekBiker.Category, qselTrekBiker.Company, qselTrekBiker.Judge, qselTrekBiker.Total, 
(Select Count(*) FROM qselTrekBiker B WHERE B.Category = qselTrekBiker.Category AND
  B.Company = qselTrekBiker.Company  and B.[Total] >=qselTrekbiker.[Total]) AS Rank
FROM qselTrekBiker
ORDER BY qselTrekBiker.Category, qselTrekBiker.Company, qselTrekBiker.Total DESC;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

That's perfect, thanks Duane, your help is appreciated as ever. This database is full of references to help you have provided!