Ranking formula for Access 2003 2

[mrichelle]

Designing a report to rank the top 20 customers by the increase in sales. We have thousand of customers and some will always be in the top 20 and some will not. I would like to know how to create a formula or code that will rank based on the the change(increase)in sales. For instance, today at 1 pm there are 20 customers with the largest sales increases, the report sees the 20 customers and then ranks them. At 3pm some of the sales data has changed for a customer, the report then sees the new sales data, then spits out a new list of 20 largest increases and proceeds to rank again, 1 - 20. So each time sales information is changed, a different top 20 is generated and a different rank is determined. We are looking at the change based on year. Formula is 2007 sales - 2006 sales which shows us who has the largest increase. Hope I did not confuse anyone.
Thanks in advance!

 

[maxhugen]

In the query behind your report, add a calculated field something like:

PercentIncrease: ([2007Sales]-[2006Sales])/[2006Sales]

Format this as a percentage.

In the Query, right-click to select Properties, and set Top Values to 20.

You may need to use another query or two to get the total sales for 2006 and 2007, and then use these in the report's query.

Max Hugen
Australia

 

[mrichelle]

Max, thanks for your help. I really appreciate the percentage formula, that will be very useful. My report looks good and is ranking the customer correctly based on the largest increase, but I have a column that says "2007 Rank" and I need the numbers 1-20 to enter that column based on the way they are listed. For instance, if Customer D has the largest increase then its rank is 1, if customer L has the second largest increase then in the column needs to be a 2, and if customer S has the third largest, in the column should be 3, and so on...So just as it is listing then based on the largest increase (which I have checked to make sure was correct) it also need to put the rank number in the 2007 rank column. So basically what I am looking for is a formula to get the rank numbers in the column without doing it manually. Hope I did not confuse. Sorry I was not clear enough on the first thread.

 

[VRoscioli]

Try looking here:

http://support.microsoft.com/kb/120608

-V