Ranking Records in Access

I am trying to create a query that will rank our offices by how they perform in several key
business indicators. The total "Score" will be the sum of their ranking in each of these
individual categories. The office with lowest "Score" is essentially our best performing office
for that week.

I have attached a spreadsheet with some sample data. The real query will have 7 or 8 fields
so I simplified it.

The fields I used in the sample are:

DimDate.WeekEnding, Sales.OfficeKey, Sales.Revenue, Sales.NetProfit, Sales.TimetoFill and
Sales.FillPercentage

I want to group where DimDate.DateKey=Sales.DateKey

Note that most categories have the largest value as the highest ranking, but
TimetoFill has the lowest value as the top ranked value.

I am relatively new to Access and am having trouble getting a simple ranking of an individual
category to work....I don't know where to begin to get the whole thing working.

In the example I attached, Office 2 is the top office for WeekEnding 11/26 and Office 1 is the top
performer in WeekEnding 12/3

Any suggestions?

Thank you!
Sample-Data.xlsx
jrflanaganAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hnasrCommented:
Try this: used 3 ranks.
table rank_keys (WeekEnding,      OfficeKey,      Revenue,      NetProfit,      TimetoFill)
WeekEnding      OfficeKey      Revenue      NetProfit      TimetoFill
26/11/2011      1      10000      3000      10
26/11/2011      2      12000      2500      5
26/11/2011      3      8000      3500      6
26/11/2011      4      9000      2000      8
03/12/2011      1      10000      3500      5
03/12/2011      2      13000      3000      7
03/12/2011      3      10000      2000      3
03/12/2011      4      9000      2500      6

Query:
SELECT WeekEnding, officekey,Revenue, RevenueRank, NetProfit, NetProfitRank, TimetoFill, TimetoFillRank, (RevenueRank + NetProfitRank +  TimetoFillRank) as TotalPoints
FROM
(SELECT a.WeekEnding as WeekEnding,a.officekey AS officekey, a.Revenue AS revenue, a.NetProfit AS NetProfit, a.TimetoFill AS TimetoFill,
 (select count(b.officekey) + 1  from rank_keys b where b.Revenue > a.Revenue and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS RevenueRank,
 (select count(b.officekey) + 1  from rank_keys b where b.NetProfit > a.NetProfit and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS NetProfitRank,
(select count(b.officekey) + 1  from rank_keys b where b.TimetoFill < a.TimetoFill and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS TimetoFillRank
FROM rank_keys AS a);

Query as code snippet:
 
SELECT WeekEnding, officekey,Revenue, RevenueRank, NetProfit, NetProfitRank, TimetoFill, TimetoFillRank, (RevenueRank + NetProfitRank +  TimetoFillRank) as TotalPoints
FROM
(SELECT a.WeekEnding as WeekEnding,a.officekey AS officekey, a.Revenue AS revenue, a.NetProfit AS NetProfit, a.TimetoFill AS TimetoFill,
 (select count(b.officekey) + 1  from rank_keys b where b.Revenue > a.Revenue and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS RevenueRank,
 (select count(b.officekey) + 1  from rank_keys b where b.NetProfit > a.NetProfit and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS NetProfitRank, 
(select count(b.officekey) + 1  from rank_keys b where b.TimetoFill < a.TimetoFill and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS TimetoFillRank
FROM rank_keys AS a);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hnasrCommented:
Result:
 
WeekEnding	officekey	Revenue	RevenueRank	NetProfit	NetProfitRank	TimetoFill	TimetoFillRank	TotalPoints
26/11/2011	1	10000	2	3000	2	10	4	8
26/11/2011	2	12000	1	2500	3	5	1	5
26/11/2011	3	8000	4	3500	1	6	2	7
26/11/2011	4	9000	3	2000	4	8	3	10
03/12/2011	1	10000	2	3500	1	5	2	5
03/12/2011	2	13000	1	3000	2	7	4	7
03/12/2011	3	10000	2	2000	4	3	1	7
03/12/2011	4	9000	4	2500	3	6	3	10

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your query should be something like:
SELECT a.WeekEnding, a.officekey,
            a.Revenue, (select count(b.officekey)+1  from RankingRecords b where b.Revenue > a.Revenue and b.WeekEnding=a.WeekEnding) AS RevenueRank, 
            a.NetProfit, (select count(b.officekey)+1 from RankingRecords b where b.NetProfit > a.NetProfit and b.WeekEnding=a.WeekEnding) AS NetProfitRank, 
            a.TimetoFill, (select count(b.officekey) +1  from RankingRecords b where b.TimetoFill < a.TimetoFill and b.WeekEnding=a.WeekEnding) AS TimetoFillRank, 
            a.FillPercentage, (select count(b.officekey)+1 from RankingRecords b where b.FillPercentage > a.FillPercentage and b.WeekEnding=a.WeekEnding) AS FillPercentageRank, 
           (RevenueRank + NetProfitRank +  TimetoFillRank + FillPercentageRank) as TotalPoints
FROM RankingRecords AS a
ORDER BY 1, 11

Open in new window


Where in ORDER BY clause the numbers represents the order of the columns in SELECT statement. In this case 1  is the WeekEnding and 11 is the TotalPoints.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jrflanaganAuthor Commented:
It worked perfectly! thank you so much!  I am a long ways from being able to write that on my own, but I've learned something in the process. Thank you again.

One more question, if you don't mind,....how would I add one more rank column to rank the overall score..."TotalPointsRank"?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Basically it will be the same, but this time you need to sum all before ranking
SELECT a.WeekEnding, a.officekey, 
            a.Revenue,  (select count(b.officekey)+1  from RankingRecords b where b.Revenue > a.Revenue and b.WeekEnding=a.WeekEnding) AS RevenueRank, 
            a.NetProfit, (select count(b.officekey)+1 from RankingRecords b where b.NetProfit > a.NetProfit and b.WeekEnding=a.WeekEnding) AS NetProfitRank, 
            a.TimetoFill, (select count(b.officekey) +1  from RankingRecords b where b.TimetoFill < a.TimetoFill and b.WeekEnding=a.WeekEnding) AS TimetoFillRank, 
            a.FillPercentage, (select count(b.officekey)+1 from RankingRecords b where b.FillPercentage > a.FillPercentage and b.WeekEnding=a.WeekEnding) AS FillPercentageRank, 
           (select count(b.officekey)+1 from RankingRecords b where (b.Revenue+b.NetProfit+b.TimetoFill+b.FillPercentage) > (a.Revenue+a.NetProfit+a.TimetoFill+a.FillPercentage) and b.WeekEnding=a.WeekEnding) AS TotalPointsRank, (RevenueRank + NetProfitRank +  TimetoFillRank + FillPercentageRank) AS TotalPoints
FROM RankingRecords AS a
ORDER BY 1, 11

Open in new window

0
hnasrCommented:
How to deal with TimeToFill in TotalPointsRank?
It is ranked differently from other columns. Lowest ranked 1, others highest ranked 1.
0
hnasrCommented:
Excluding TimeToFill:

SELECT WeekEnding, officekey,Revenue, RevenueRank, NetProfit, NetProfitRank, TimetoFill, TimetoFillRank, (Revenue + NetProfit +  TimetoFill) as TotalPoints_without_TimetoFill,TotalPointsRank
FROM
(SELECT a.WeekEnding as WeekEnding,a.officekey AS officekey, a.Revenue AS revenue, a.NetProfit AS NetProfit, a.TimetoFill AS TimetoFill ,(a.Revenue+a.NetProfit + a.TimetoFill) As TotalPoints,
(select count(b.officekey) + 1  from rank_keys b where b.Revenue > a.Revenue and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS RevenueRank ,
(select count(b.officekey) + 1  from rank_keys b where b.NetProfit > a.NetProfit and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS NetProfitRank  ,
(select count(b.officekey) + 1  from rank_keys b where b.TimetoFill < a.TimetoFill and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS TimetoFillRank,

(select count(b.officekey) + 1  from rank_keys b where (b.Revenue+b.NetProfit+0) > (a.Revenue+a.NetProfit+0) and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS TotalPointsRank


FROM rank_keys AS a);
 
SELECT WeekEnding, officekey,Revenue, RevenueRank, NetProfit, NetProfitRank, TimetoFill, TimetoFillRank, (Revenue + NetProfit +  TimetoFill) as TotalPoints_without_TimetoFill,TotalPointsRank
FROM
(SELECT a.WeekEnding as WeekEnding,a.officekey AS officekey, a.Revenue AS revenue, a.NetProfit AS NetProfit, a.TimetoFill AS TimetoFill ,(a.Revenue+a.NetProfit + a.TimetoFill) As TotalPoints,
(select count(b.officekey) + 1  from rank_keys b where b.Revenue > a.Revenue and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS RevenueRank ,
(select count(b.officekey) + 1  from rank_keys b where b.NetProfit > a.NetProfit and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS NetProfitRank  ,
(select count(b.officekey) + 1  from rank_keys b where b.TimetoFill < a.TimetoFill and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS TimetoFillRank,

(select count(b.officekey) + 1  from rank_keys b where (b.Revenue+b.NetProfit+0) > (a.Revenue+a.NetProfit+0) and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS TotalPointsRank


FROM rank_keys AS a);

Open in new window

Result:

WeekEnding	officekey	Revenue	RevenueRank	NetProfit	NetProfitRank	TimetoFill	TimetoFillRank	TotalPoints_without_TimetoFill	TotalPointsRank
26/11/2011	1	10000	2	3000	2	10	4	13010	2
26/11/2011	2	12000	1	2500	3	5	1	14505	1
26/11/2011	3	8000	4	3500	1	6	2	11506	3
26/11/2011	4	9000	3	2000	4	8	3	11008	4
03/12/2011	1	10000	2	3500	1	5	2	13505	2
03/12/2011	2	13000	1	3000	2	7	4	16007	1
03/12/2011	3	10000	2	2000	4	3	1	12003	3
03/12/2011	4	9000	4	2500	3	6	3	11506	4

Open in new window

0
jrflanaganAuthor Commented:
Is there a way to base TotalPointsRank directly off of the results in TotalPoints? or maybe off the sum of the inidividual rank felds used to create Total Points?  
0
hnasrCommented:
"Is there a way to base TotalPointsRank directly off of the results in TotalPoints? "
Yes, if it exists like other fields. But as a calculated field it should not be included in the table.

If you have the data manipulated in Excel which is linked to an access table, then you can safely do that.

Query:
 
SELECT WeekEnding, officekey, Revenue, RevenueRank, NetProfit, NetProfitRank, TimetoFill, TimetoFillRank, (Revenue+NetProfit+TimetoFill) AS TotalPoints_without_TimetoFill, TotalPointsRank
FROM (SELECT a.WeekEnding AS WeekEnding, a.officekey AS officekey, a.Revenue AS revenue, a.NetProfit AS NetProfit, a.TimetoFill AS TimetoFill, (a.Revenue+a.NetProfit+a.TimetoFill) AS TotalPoints,
 (select count(b.officekey) + 1  from rank_keys b where b.Revenue > a.Revenue and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS RevenueRank,
 (select count(b.officekey) + 1  from rank_keys b where b.NetProfit > a.NetProfit and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS NetProfitRank,
 (select count(b.officekey) + 1  from rank_keys b where b.TimetoFill < a.TimetoFill and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS TimetoFillRank, 
(select count(b.officekey) + 1  from rank_keys b where b.TotalPoints > a.TotalPoints and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS TotalPointsRank FROM rank_keys AS a)  AS x;

Open in new window

0
jrflanaganAuthor Commented:
alright, it's not crucial that I have that in the query. As I expand, the number of fields included in the total to include data from other tables, how would I include that in the query?
0
hnasrCommented:
Just add more (select ....) as in bold, for each field as follows.
.......
(select count(b.officekey) + 1  from rank_keys b where b.Field1 > a.Field1 and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS Field1Rank,

(select count(b.officekey) + 1  from rank_keys b where b.Field2 > a.Field2 and DatePart("ww",b.WeekEnding)=DatePart("ww",a.WeekEnding)) AS Field2Rank

FROM rank_keys AS a)  AS x;

In the first Select included the added fields.
SELECT WeekEnding, officekey, Revenue, RevenueRank, NetProfit, NetProfitRank, TimetoFill, TimetoFillRank, (Revenue+NetProfit+TimetoFill) AS TotalPoints_without_TimetoFill,Field1Rank, Field2Rank FROM
(......)

You can include the totla as suggested either adding fields or a total from table.
0
hnasrCommented:
"include data from other tables, how would I include that in the query? "

Start a new question for this new requirement unless you can create one query from the joined tables. So you can follow the same idea with the new query as one table.
0
jrflanaganAuthor Commented:
ok, thank you very much
0
jrflanaganAuthor Commented:
very easy to follow solution
0
hnasrCommented:
Welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.