Ranking Records in a Select Query

S

Swiss

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.
 
J

JohnFol

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(*)
 
S

Swiss

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 said:
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 said:
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.
 
J

JohnFol

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 said:
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 said:
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 said:
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.
 
S

Swiss

Thanks. That almost does it but unfortunatley, the ranking is back to front,
i.e. the lower the value of CountOfFruit, the higher the ranking. It should
be the the higher the value of CountOfFruit, the lower the ranking! Sorry,
I'd try to amend myself but I have to admit I don't really understand how the
expression works!

Finally, is there a simple way to return the record number against each
record in the query output?

Thanks for all your help.

JohnFol said:
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 said:
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 said:
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(*)


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.
 
J

John Spencer (MVP)

Change the Less Than sign to Greater Than.


Thanks. That almost does it but unfortunatley, the ranking is back to front,
i.e. the lower the value of CountOfFruit, the higher the ranking. It should
be the the higher the value of CountOfFruit, the lower the ranking! Sorry,
I'd try to amend myself but I have to admit I don't really understand how the
expression works!

Finally, is there a simple way to return the record number against each
record in the query output?

Thanks for all your help.

JohnFol said:
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 said:
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?

:

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


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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top