Ranking within a query

  • Thread starter Thread starter Jack Spratt
  • Start date Start date
J

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
 
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]
Click to expand...

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

Steve Schapel said:
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]
Click to expand...

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
Click to expand...
Click to expand...
 
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

Steve Schapel said:
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]
Click to expand...

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
Click to expand...
Click to expand...
 
Back
Top