Ranking Records in query - can I get it to say BEST and WORST?

[lmn]

Hello,
I have a query that ranks records and I'd like it to say BEST for the highest and WORST for the lowest. If I had a set # of vendors - I could always say that the Rank of 1 = BEST and the Rank of - let's say - 12 was the WORST - but one quarter I may have 12 and the next quarter I may have 13.

Is there a way to write an IIf statement for this?

I thought this would work but it doesn't like it. I am writing it in the same query where they are being ranked (not even sure that is possible)

(IIf([Ranking]=MIN([Ranking]),"BEST","&quot)

 

[WildHare]

What characterizes your "BEST" or "WORST"? Altitude? Number of Returns? Amount of Flash on webpage?

Once we know that, we can give you the right function.

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[TomCologne]

This is an example from my dbTest. It can certainly be done better, but it may get you started:

First, I created Query2 to total Min and Max of tblTest1.[no], then

SELECT tblTest1.data2 AS Best, tblTest1_1.data2 AS Worst
FROM (Query2 INNER JOIN tblTest1 ON Query2.MinOfno = tblTest1.[no]) INNER JOIN tblTest1 AS tblTest1_1 ON Query2.MaxOfno = tblTest1_1.[no];

to display the corresponding value from tblTest1.[data2], ie vendor name, as Best and Worst.

You're not alone,

TomCologne

 

[lmn]

I will probably get laughed at for this but I created a query where I determined the MIN and the MAX. I subsequently attached that query to my query that creates the rankings and other results (for the BIG report) and it worked out nicely. I wrote an IIf statement saying if the score was equal to the MIN score - say WORST (actually, that's a bad word to them - they say it sounds so negative - so it will be changed to something more politically correct (I LOVE SAN FRANCISCO!!!!!) - but if it is the MAX score it will say Best!

:-D

 

[WildHare]

LMN, what you did is correct. There's no shame in using a query as one of the 'sources' in another query.

As far as your replacement for "worst", how about "Greatest Potential for Improvement" ???

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm