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