Ranking within a query

[Jack Spratt]

Hi & TIA for any assistance

I have a query that ranks a set of scores from highest to lowest as below

Name Score
John 89
Matthew 83
Luke 76
Mary 42

How do I create another column that numbers them from Number 1 (as in first)
to number whatever (as in last)
Name Score Rank
John 89 1
Matthew 83 2
Luke 76 3
Mary 42 4

Any help would be much appreciated

Ta

 

[Van T. Dinh]

See Stephen Lebans' RowNumber:

<http://www.lebans.com/rownumber.htm>

HTH
Van T. Dinh
MVP (Access)

 

[Steve Schapel]

Jack,

Make a query with two instances of your table added to it. Acces will
name the second instance YourTable_1 (where YourTable is the name of
your table

OK, add both fields Name and Score from both tables to the query
design grid. Make it a Totals Query (select Totals from the View
menu). Leave the Totals row for the first Name field and the first
Score field set to Group By. In the Totals row of the second Name
field, enter Count, in front of the field name type Rank: and enter
Ascending in the Sort of this column. And in the Totals row of the
second Score field, put...

=[YourTable].[Score]

Done! The SQL view for the query will look a bit like this
(substituting your actual table and field names of course)...

SELECT YourTable.Name, YourTable.Score, Count(YourTable_1.Name) AS
Rank
FROM YourTable, YourTable AS YourTable_1
WHERE (((YourTable_1.Score)>=[YourTable].[Score]))
GROUP BY YourTable.Name, YourTable.Score
ORDER BY Count(YourTable_1.Name)

- Steve Schapel, Microsoft Access MVP

 

[Steve Schapel]

By the way, Jack, I meant to mention... The word 'name' is a "reserved
word" (i.e. has a special meaning) in Access, and as such it is not a
good idea to use it as the name of a field or control.

- Steve Schapel, Microsoft Access MVP

 

[Jack Spratt]

Steve

Looks all good but what about if more than one person has the same score how
will it rank them then say 1,2,2,4,5,6,6,8,9,10

Jack

Jack,

Make a query with two instances of your table added to it. Acces will
name the second instance YourTable_1 (where YourTable is the name of
your table

OK, add both fields Name and Score from both tables to the query
design grid. Make it a Totals Query (select Totals from the View
menu). Leave the Totals row for the first Name field and the first
Score field set to Group By. In the Totals row of the second Name
field, enter Count, in front of the field name type Rank: and enter
Ascending in the Sort of this column. And in the Totals row of the
second Score field, put...

=[YourTable].[Score]

Done! The SQL view for the query will look a bit like this
(substituting your actual table and field names of course)...

SELECT YourTable.Name, YourTable.Score, Count(YourTable_1.Name) AS
Rank
FROM YourTable, YourTable AS YourTable_1
WHERE (((YourTable_1.Score)>=[YourTable].[Score]))
GROUP BY YourTable.Name, YourTable.Score
ORDER BY Count(YourTable_1.Name)

- Steve Schapel, Microsoft Access MVP

Hi & TIA for any assistance

I have a query that ranks a set of scores from highest to lowest as below

Name Score
John 89
Matthew 83
Luke 76
Mary 42

How do I create another column that numbers them from Number 1 (as in first)
to number whatever (as in last)
Name Score Rank
John 89 1
Matthew 83 2
Luke 76 3
Mary 42 4

Any help would be much appreciated

Ta

 

[Jack Spratt]

Steve

Looks all good but what about if more than one person has the same score how
will it rank them then say 1,2,2,4,5,6,6,8,9,10

Jack

Jack,

Make a query with two instances of your table added to it. Acces will
name the second instance YourTable_1 (where YourTable is the name of
your table

OK, add both fields Name and Score from both tables to the query
design grid. Make it a Totals Query (select Totals from the View
menu). Leave the Totals row for the first Name field and the first
Score field set to Group By. In the Totals row of the second Name
field, enter Count, in front of the field name type Rank: and enter
Ascending in the Sort of this column. And in the Totals row of the
second Score field, put...

=[YourTable].[Score]

Done! The SQL view for the query will look a bit like this
(substituting your actual table and field names of course)...

SELECT YourTable.Name, YourTable.Score, Count(YourTable_1.Name) AS
Rank
FROM YourTable, YourTable AS YourTable_1
WHERE (((YourTable_1.Score)>=[YourTable].[Score]))
GROUP BY YourTable.Name, YourTable.Score
ORDER BY Count(YourTable_1.Name)

- Steve Schapel, Microsoft Access MVP

Hi & TIA for any assistance

I have a query that ranks a set of scores from highest to lowest as below

Name Score
John 89
Matthew 83
Luke 76
Mary 42

How do I create another column that numbers them from Number 1 (as in first)
to number whatever (as in last)
Name Score Rank
John 89 1
Matthew 83 2
Luke 76 3
Mary 42 4

Any help would be much appreciated

Ta