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.Date Key
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
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.Date
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your query should be something like:
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.
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
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.
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"?
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
How to deal with TimeToFill in TotalPointsRank?
It is ranked differently from other columns. Lowest ranked 1, others highest ranked 1.
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_Timeto Fill,Total PointsRank
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.We ekEnding)) AS RevenueRank ,
(select count(b.officekey) + 1 from rank_keys b where b.NetProfit > a.NetProfit and DatePart("ww",b.WeekEnding )=DatePart ("ww",a.We ekEnding)) AS NetProfitRank ,
(select count(b.officekey) + 1 from rank_keys b where b.TimetoFill < a.TimetoFill and DatePart("ww",b.WeekEnding )=DatePart ("ww",a.We ekEnding)) 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.We ekEnding)) AS TotalPointsRank
FROM rank_keys AS a);
SELECT WeekEnding, officekey,Revenue, RevenueRank, NetProfit, NetProfitRank, TimetoFill, TimetoFillRank, (Revenue + NetProfit + TimetoFill) as TotalPoints_without_Timeto
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
(select count(b.officekey) + 1 from rank_keys b where b.NetProfit > a.NetProfit and DatePart("ww",b.WeekEnding
(select count(b.officekey) + 1 from rank_keys b where b.TimetoFill < a.TimetoFill and DatePart("ww",b.WeekEnding
(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
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);
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
ASKER
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:
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;
ASKER
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.We ekEnding)) AS Field1Rank,
(select count(b.officekey) + 1 from rank_keys b where b.Field2 > a.Field2 and DatePart("ww",b.WeekEnding )=DatePart ("ww",a.We ekEnding)) 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+TimetoF ill) AS TotalPoints_without_Timeto Fill,Field1Rank, Field2Rank FROM
(......)
You can include the totla as suggested either adding fields or a total from table.
.......
(select count(b.officekey) + 1 from rank_keys b where b.Field1 > a.Field1 and DatePart("ww",b.WeekEnding
(select count(b.officekey) + 1 from rank_keys b where b.Field2 > a.Field2 and DatePart("ww",b.WeekEnding
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+TimetoF
(......)
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.
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.
ASKER
ok, thank you very much
ASKER
very easy to follow solution
Welcome!
Open in new window