Link to home
Start Free TrialLog in
Avatar of jrflanagan
jrflanagan

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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.
Avatar of jrflanagan
jrflanagan

ASKER

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

How to deal with TimeToFill in TotalPointsRank?
It is ranked differently from other columns. Lowest ranked 1, others highest ranked 1.
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

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

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?
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.
"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.
ok, thank you very much
very easy to follow solution
Welcome!