Ranking Records in a Select Query

[Guest]

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.

 

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

 

[Guest]

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.

 

[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;

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.

 

[Guest]

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.

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;

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.

 

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

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;

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.