×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Ranking again

Ranking again

Ranking again

(OP)
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

CODE -->

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







RE: Ranking again

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

In other words, if you run this:

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


Do you get anything else than just:
MyCount
1


---- Andy

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

RE: Ranking again

(OP)
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];

RE: Ranking again

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:

CODE --> SQL

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

RE: Ranking again

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

RE: Ranking again

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close