Ranking again 1

[TrekBiker]

Sorry to come back again as Duane gave me a perfect solution to my last request. I've tried replicating it with a different starting query that needs results to be ranked, but every time get Rank = 1. The query structure looks very much the same so I'm constantly missing something.

This time the query called Simplify looks like this

My attempt at a ranking query looks like this

SELECT Simplify.Category, Simplify.Company, Simplify.[Overall Average], 
(Select Count(*) FROM Simplify B WHERE B.Category = Simplify.Category AND
  B.Company = Simplify.Company  and B.[Overall Average] >=Simplify.[Overall Average]) AS Rank
FROM Simplify
ORDER BY Simplify.Category, Simplify.Company, Simplify.[Overall Average] DESC;

But as I said it produces this result

 

[Andrzejek]

Do you have any records in Simplify table/query with more than one record per Category / Company?

In other words, if you run this:[tt]

Select Distinct Count(*) As MyCount
FROM Simplify B, Simplify
WHERE B.Category = Simplify.Category
AND B.Company = Simplify.Company
and B.[Overall Average] > =Simplify.[Overall Average]) [/tt]

Do you get anything else than just:[tt]
MyCount
1[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks Andy.

Minor changes needed but this gave 127

Select Distinct Count(*) As MyCount
FROM Simplify B, Simplify
WHERE B.Category = Simplify.Category
AND B.Company = Simplify.Company
and B.[Overall Average] >= Simplify.[Overall Average];

 

[dhookom]

Since all of your companies seem to be unique to a category I think you simply need to remove the Company field from the JOIN:

SELECT Simplify.Category, Simplify.Company, Simplify.[Overall Average], 
(Select Count(*) FROM Simplify B WHERE B.Category = Simplify.Category  
 and B.[Overall Average] >=Simplify.[Overall Average]) AS Rank
FROM Simplify
ORDER BY Simplify.Category, Simplify.[Overall Average] DESC;

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

 

[TrekBiker]

Big thanks, Duane, yet again.
Still grappling with understanding this type of query so hope not to have to keep returning for help with them.
Hope your recovery is progressing well.

 

[dhookom]

Thanks TrekBiker,
I'm hoping to get out on my Specialized gravel today for the first time since June 6th (two days prior to my heart attack).

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