Re: Ranking Records in a Select Query

Agreed, but it does give you the data set. When you actually display the
records (form/report) getting the position in the recordset / rank is easy.
If you want the results in a query ready for export, try this:

Save this first as FruitsQuery
SELECT Fruits.Fruit, Count(Fruits.Fruit) AS CountOfFruit
FROM Fruits
GROUP BY Fruits.Fruit;


SELECT DCount("*","FruitsQuery","CountOfFruit<" & [countOfFruit])+1 AS
Expr1, FruitsQuery.Fruit, FruitsQuery.CountOfFruit
FROM FruitsQuery
ORDER BY FruitsQuery.CountOfFruit;





"Swiss" <Swiss@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D24ECDB7-DB7A-46C8-98D0-9167F929CFFD@xxxxxxxxxxxxxxxx
> Thanks for that. I realise that I could sort the results by quantity but
> that doesn't achieve the objective of:
>
> 1) Displaying a ranking next to each entry (this is a top sellers
> "chart").
> 2) Handling the situation where 2 or more records have the same quantity
> so
> that their "rank" is the same and should be displayed as such.
>
> Sorry, should have been explicit about this in original post.
>
> To expand the example, I would want the following recordset:
>
> Apples (6)
> Pears (14)
> Bananas (3)
> Grapes (6)
>
> To appear as follows:
>
> Rank,Item
> 1,Pears
> 2,Apples
> 2,Grapes
> 3,Bananas
>
> Any thoughts?
>
> "JohnFol" wrote:
>
>> But to get the ranking you need to know the order? In your example you
>> are
>> ranking based upon a count in descending order.
>>
>> You could do a simple GroupBy Query to get the results in that order
>>
>> Select Fruit, Count(*) from MyTable GroupBy Count(*)
>>
>>
>> "Swiss" <Swiss@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:E4290F32-66C4-47D4-92D5-34DF1FF375E3@xxxxxxxxxxxxxxxx
>> > Anyone know of a way to give a ranking to records returned from a
>> > select
>> > query regardless of sort order? E.g. in a list of records:
>> >
>> > Apples (6) [2]
>> > Pears (14) [1]
>> > Bananas (3) [3]
>> >
>> > The ranking in [ ] would be returned based on the quantity in ( ). I
>> > know
>> > there is an Excel function (RANK) that suits but can't find an access
>> > equivalent.
>> >
>> > I'm running Access 2002.
>>
>>
>>


.

Relevant Pages

  • RE: How EXACTLY does Indexing Service determine rank
    ... the point that they are adjacent becoming a phrase and raising the rank ... The ranking mechanism is weighted so that the more highly inflected ... the linguistic engine and ranking algorithm ... Indexing Service is based on ranking formulas that are used everywhere from ...
    (microsoft.public.inetserver.indexserver)
  • Re: Increasing your PAGE RANK
    ... >> Participating in link schemes is a way to increase page rank, ... so the site 'spam ranking' will ... I have a "useful links" page on one site for people who want ... > to do a link exchange. ...
    (alt.internet.search-engines)
  • Re: Integrating results from different pages? (No luck w/ INDEX &
    ... ranking for every player that has a ranking. ... Most of my source come in the format "Rank. ... Lastname" match a cell that is a formula that gives the same result? ... > Now you have all the names in sheet3 ...
    (microsoft.public.excel.misc)
  • RE: Ranking different groups in one column
    ... Thank you for the ranking different groups within one colmun formula: ... As you see, three regions, a bunch of store names and scores. ... Gary''s Student - gsnu200776 ... a.n.other user from this datasheet and just 'pulling' the rank no into the ...
    (microsoft.public.excel.misc)
  • Re: Sorting functionality
    ... application specific sql functionality. ... I have to implement a ranking ... a rank must be assigned to each tuple depending ... separate attribute in the same table. ...
    (comp.databases)
Loading