Get rank based on Productivity

B

Boss

Hi,

I have almost done with the entire project with the help of things i have
learned in this forum...

Please help me get this... I have a table with foll details.

EMP name - No. of cases done - total time taken - standard time -
prodtivty in %. In five different columns.

How can i get the rank in a new column. please help me get this.

Thanks!
Boss
 
J

John Spencer MVP

Ranked on what? Productivity?

You can build a ranking query by referencing your table (or query) twice in
the FROM clause. See the example below

SELECT A.[Emp Name], A.[No. of Cases Done]
, A.[Total Time Taken], A.[Standard Time]
, A.Productivity
, 1 + Count(B.Productivity) as Rank
FROM [YourTableOrQuery] as A LEFT JOIN [YourTableOrQuery] as B
On A.Productivity > B.Productivity
GROUP BY A.[Emp Name], A.[No. of Cases Done]
, A.[Total Time Taken], A.[Standard Time]
, A.Productivity

If you don't know how to build that query in SQL view then post back for
instructions. You may be able to paste the sample above and just replace
YourTableOrQuery with the name of your table or query that provides the data.

On the other hand if you just want the information for a report there may be
no need to calculate the rank. Depends on if you are looking for a one-up
sequential number for each detail line.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
B

Boss

Thanks a lot for the reply.. it really helped... I missed one part can we
have the productivity team wise... I do have a column TL in the table.

Thanks!

John Spencer MVP said:
Ranked on what? Productivity?

You can build a ranking query by referencing your table (or query) twice in
the FROM clause. See the example below

SELECT A.[Emp Name], A.[No. of Cases Done]
, A.[Total Time Taken], A.[Standard Time]
, A.Productivity
, 1 + Count(B.Productivity) as Rank
FROM [YourTableOrQuery] as A LEFT JOIN [YourTableOrQuery] as B
On A.Productivity > B.Productivity
GROUP BY A.[Emp Name], A.[No. of Cases Done]
, A.[Total Time Taken], A.[Standard Time]
, A.Productivity

If you don't know how to build that query in SQL view then post back for
instructions. You may be able to paste the sample above and just replace
YourTableOrQuery with the name of your table or query that provides the data.

On the other hand if you just want the information for a report there may be
no need to calculate the rank. Depends on if you are looking for a one-up
sequential number for each detail line.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi,

I have almost done with the entire project with the help of things i have
learned in this forum...

Please help me get this... I have a table with foll details.

EMP name - No. of cases done - total time taken - standard time -
prodtivty in %. In five different columns.

How can i get the rank in a new column. please help me get this.

Thanks!
Boss
Click to expand...
Click to expand...
 
J

John Spencer MVP

I think that you want to add the TL field to the FROM clause and get rankings
within each group as defined by TL. If that is correct then try the following.

SELECT A.[Emp Name], A.[No. of Cases Done]
, A.[Total Time Taken], A.[Standard Time]
, A.Productivity
, 1 + Count(B.Productivity) as Rank
FROM [YourTableOrQuery] as A LEFT JOIN [YourTableOrQuery] as B
On A.Productivity > B.Productivity
AND A.TL = B.TL
GROUP BY A.[Emp Name], A.[No. of Cases Done]
, A.[Total Time Taken], A.[Standard Time]
, A.Productivity

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Ranked on what? Productivity?

You can build a ranking query by referencing your table (or query) twice
in the FROM clause. See the example below

SELECT A.[Emp Name], A.[No. of Cases Done]
, A.[Total Time Taken], A.[Standard Time]
, A.Productivity
, 1 + Count(B.Productivity) as Rank
FROM [YourTableOrQuery] as A LEFT JOIN [YourTableOrQuery] as B
On A.Productivity > B.Productivity
GROUP BY A.[Emp Name], A.[No. of Cases Done]
, A.[Total Time Taken], A.[Standard Time]
, A.Productivity

If you don't know how to build that query in SQL view then post back for
instructions. You may be able to paste the sample above and just
replace YourTableOrQuery with the name of your table or query that
provides the data.

On the other hand if you just want the information for a report there
may be no need to calculate the rank. Depends on if you are looking for
a one-up sequential number for each detail line.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi,

I have almost done with the entire project with the help of things i
have learned in this forum...

Please help me get this... I have a table with foll details.

EMP name - No. of cases done - total time taken - standard time -
prodtivty in %. In five different columns.

How can i get the rank in a new column. please help me get this.

Thanks!
Boss
Click to expand...
Click to expand...
 
B

Boss

Yes this is what i was looking for... thank you for your time...



John Spencer MVP said:
I think that you want to add the TL field to the FROM clause and get rankings
within each group as defined by TL. If that is correct then try the following.

SELECT A.[Emp Name], A.[No. of Cases Done]
, A.[Total Time Taken], A.[Standard Time]
, A.Productivity
, 1 + Count(B.Productivity) as Rank
FROM [YourTableOrQuery] as A LEFT JOIN [YourTableOrQuery] as B
On A.Productivity > B.Productivity
AND A.TL = B.TL
GROUP BY A.[Emp Name], A.[No. of Cases Done]
, A.[Total Time Taken], A.[Standard Time]
, A.Productivity

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Ranked on what? Productivity?

You can build a ranking query by referencing your table (or query) twice
in the FROM clause. See the example below

SELECT A.[Emp Name], A.[No. of Cases Done]
, A.[Total Time Taken], A.[Standard Time]
, A.Productivity
, 1 + Count(B.Productivity) as Rank
FROM [YourTableOrQuery] as A LEFT JOIN [YourTableOrQuery] as B
On A.Productivity > B.Productivity
GROUP BY A.[Emp Name], A.[No. of Cases Done]
, A.[Total Time Taken], A.[Standard Time]
, A.Productivity

If you don't know how to build that query in SQL view then post back for
instructions. You may be able to paste the sample above and just
replace YourTableOrQuery with the name of your table or query that
provides the data.

On the other hand if you just want the information for a report there
may be no need to calculate the rank. Depends on if you are looking for
a one-up sequential number for each detail line.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi,

I have almost done with the entire project with the help of things i
have learned in this forum...

Please help me get this... I have a table with foll details.

EMP name - No. of cases done - total time taken - standard time -
prodtivty in %. In five different columns.

How can i get the rank in a new column. please help me get this.

Thanks!
Boss
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Get rank on based on productivity 0
Ranking without skipping ranks and percentile 3
Requesting Help Ranking Sales Values..... 6
Ranking based on Multiple Data Fields 10
Detemine sales order based on plac changes and not sales figures -mathmatic problem to solve 1
Sales Ranks 1
ODBC error 100 when accessing Bookkeeper version 15 software (accounting software using Access dbase) 0
Ranking in Access Query by Dates 1

Top