Ranking results of a Query

[twalsh]

I have set of queries running for employee performance ranking culminating in
a final query that shows their name, employee ID and overall score, i would
like the final step to also add a column with rank, so the person with the
highest score has a 1, next highest has a 2 etc......
I am kind of new to this, like everyone else who posts it seems so lamens
terms are appreciated.
If it helps the name of the query is "Query-Shift Bid Part C", and the
columns are "First", "Last","Login","Hours","QA" and "Score"

I want "Score" ranked from highest to lowest....

any and all help is much appreciated...

thanks
tw

 

[Amy Blankenship]

I have set of queries running for employee performance ranking culminating
in
a final query that shows their name, employee ID and overall score, i
would
like the final step to also add a column with rank, so the person with the
highest score has a 1, next highest has a 2 etc......
I am kind of new to this, like everyone else who posts it seems so lamens
terms are appreciated.
If it helps the name of the query is "Query-Shift Bid Part C", and the
columns are "First", "Last","Login","Hours","QA" and "Score"

I want "Score" ranked from highest to lowest....

http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

 

[John Spencer]

Assumption First and Last are sufficient to uniquely identify an
individual - that is you don't have two (or more) Tom Smith working

The following query cannot be built in design view, but must be built in SQL
view
SELECT q.First, q.Last, q.Login, q.Hours, q.Score
, Count(Q2.Score) + 1 as Rank
FROM [Query-Shift Bid Part C] as Q
INNER JOIN [Query-Shift Bid Part C] as Q2
On Q.First = Q2.First
And Q.Last= Q2.Last
And Q.Score < Q2.Score

If you don't do SQL then
-- Open a new query
-- Add the query Query-Shift Bid Part C to the query TWO TIMES
-- Select First Last Login Hours and Score from one query
-- Select Score from the other query
-- Select View: Totals from the menu
-- Change Group By to Count under the second Score
-- Set up a relationship between the two tables by dragging from First to
First, Last to Last, and Score to Score
-- Select View: SQL from the menu
-- Find the bit of code that says [Query-Shift Bid Part C].Score =
[Query-Shift Bid Part C_1].Score and change the equal sign to <
-- Run the query.
-- If you don't like the ranking starting at zero, then modify the SQL
statement to add 1 to the count (see above for a hint)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michel Walsh]

Probably a typo, should be LEFT JOIN rather than INNER JOIN, otherwise
Q.Score < Q2.Score would be without match for the smallest Q.Score value.

And, as I often do too, forgot to paste back the GROUP BY clause


SELECT q.First, q.Last, q.Login, q.Hours, q.Score
, Count(Q2.Score) + 1 as Rank

FROM [Query-Shift Bid Part C] as Q
LEFT JOIN [Query-Shift Bid Part C] as Q2
On Q.First = Q2.First
And Q.Last= Q2.Last
And Q.Score < Q2.Score

GROUP BY q.First, q.Last, q.Login, q.Hours, q.Score




Vanderghast, Access MVP

Assumption First and Last are sufficient to uniquely identify an
individual - that is you don't have two (or more) Tom Smith working

The following query cannot be built in design view, but must be built in
SQL view
SELECT q.First, q.Last, q.Login, q.Hours, q.Score
, Count(Q2.Score) + 1 as Rank
FROM [Query-Shift Bid Part C] as Q
INNER JOIN [Query-Shift Bid Part C] as Q2
On Q.First = Q2.First
And Q.Last= Q2.Last
And Q.Score < Q2.Score

If you don't do SQL then
-- Open a new query
-- Add the query Query-Shift Bid Part C to the query TWO TIMES
-- Select First Last Login Hours and Score from one query
-- Select Score from the other query
-- Select View: Totals from the menu
-- Change Group By to Count under the second Score
-- Set up a relationship between the two tables by dragging from First to
First, Last to Last, and Score to Score
-- Select View: SQL from the menu
-- Find the bit of code that says [Query-Shift Bid Part C].Score =
[Query-Shift Bid Part C_1].Score and change the equal sign to <
-- Run the query.
-- If you don't like the ranking starting at zero, then modify the SQL
statement to add 1 to the count (see above for a hint)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have set of queries running for employee performance ranking culminating
in
a final query that shows their name, employee ID and overall score, i
would
like the final step to also add a column with rank, so the person with
the
highest score has a 1, next highest has a 2 etc......
I am kind of new to this, like everyone else who posts it seems so lamens
terms are appreciated.
If it helps the name of the query is "Query-Shift Bid Part C", and the
columns are "First", "Last","Login","Hours","QA" and "Score"

I want "Score" ranked from highest to lowest....

any and all help is much appreciated...

thanks
tw