Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to setup access to Rank

Status
Not open for further replies.

TJVFree

Technical User
Nov 22, 2010
236
US
I’m trying to put my customers in Rank from 0-5 depending on the past 3 months, 6 months, 9 months, and 1 year.

The numbers I used below are only sample numbers. If it doesn’t make sense please email me and I will get back to you right away.

Thank you for taking the time to help with this query

Customer Name
3M/ 6M/ 9M/ 12M/ Rate between 0 and 5
Joe Smith 123/ 102/ 145/ 201/ 3.1
Jan Smith 223/ 302/ 241/ 225 4.2

Below are some of the formulas I used in MS Excel.

=1-((C6-1)/SUM($D$4:$D$2002))

=5-(AJ5/MAX($AJ$3:$AJ$10000)*5)
 
Presumeably you want to rank them on the rating field. Can you not just right the click the field and sort them in an ascending order?

Thanks,
Rebecca
 
Yes i can do that, but I'm trying to do a ranking from 0-5
 
You aren't in Excel anymore so you should normalize your table structure. What is your current table structure and what is the logic behind what you want to calculate?

Duane
Hook'D on Access
MS Access MVP
 
Why would that not rank them from 0-5 perhaps I'm missing something. You could also use the order by property and order on the rank field...

Thanks,
Rebecca
 
Oh I understand now actually - because the rating is also 0-5 I still thought you meant that. Is this data you want to rank in a form or is it just the table?

Thanks,
Rebecca
 
So let me just make sure I understand, where do you want the rank of that customer to be kept? Is there a rank field?

Thanks,
Rebecca
 
CoreyVl, RMcCallan . . .

Could either of you provide a longer version of this [blue]rating method[/blue] only the two of you seem to understand?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
I believe CoreyVI wants to effectively put them in order and then give the top 5 (with the highest ratings) ranks from 1-5...

Thanks,
Rebecca
 
I think I just did... put them from highest to lowest (in order...) and then put 1, 2, 3, 4, 5 against the ones which are the 5 highest...

Thanks,
Rebecca
 
RMcCallan,
We all understand that much. No one has identified clearly what is "highest to lowest". The OP had suggested:

=1-((C6-1)/SUM($D$4:$D$2002))
and
=5-(AJ5/MAX($AJ$3:$AJ$10000)*5)

This isn't Excel, it's an Access database with table and field names, records, and expressions/calculations.

I'm not sure what I can say that better describes what someone needs to be able to provide an accurate Access answer.

Duane
Hook'D on Access
MS Access MVP
 
I think CoreyVI simply included those formulas to show exactly what he wanted to accomplish... And I think it would make sense to define highest to lowest as customers with a rating of 5 would be highest and customers with ratings of 0 would be lowest.

Thanks,
Rebecca
 
I would expect to see some type of expression that involves table and field names like:
Code:
1-(([Table1].[FieldA]-1)/Sum([Table1].[FieldB]))
This along with sample records with the desired display would help.


Duane
Hook'D on Access
MS Access MVP
 
Well, I don't know, I was just trying to help explain what he means but he isn't even replying now so can't really do much more...

Thanks,
Rebecca
 
I agree the tables should be normalized. If you had to do it with this design, I would do it in two queries just to make it cleaner:
qryCustomerTotals
Code:
SELECT 
 tblOne.ID, 
 [3M]+[6M]+[9m]+[12M] AS CustomerTotal
FROM 
 tblOne
ORDER BY 
 [3M]+[6M]+[9m]+[12M];

Then
Code:
SELECT 
 qryCustomerTotals.ID, 
 qryCustomerTotals.CustomerTotal, 
 (Select count(*) from qryCustomerTotals as B where qryCustomerTotals.CustomerTotal > B.customerTotal) AS Rank, 
 (Select count(*) from qryCustomerTotals) AS TotalRecords,  [Rank]/([TotalRecords]-1)*5 AS 0to5Rank
FROM qryCustomerTotals;

The output would look something like:
Code:
ID  CustomerTotal  Rank	 TotalRecords	0to5Rank
3   100	           0    4               0
1   571	           1    4               1.67
2   991	           2    4               3.33
4   1200           3    4               5
 
That is a linear ranking from 1 to 5. More likely you want a weighted ranking.
Code:
SELECT 
 qryCustomerTotals.ID, 
 qryCustomerTotals.CustomerTotal, 
 (Select sum(customerTotal)from qryCustomerTotals) AS GrandTotal, 
 (select min(customerTotal) from qryCustomerTotals) AS MinTotal, 
 (select max(customerTotal) from qryCustomerTotals) AS MaxTotal, 
 5*(([CustomerTotal]-[minTotal])/([MaxTotal]-[MinTotal])) AS WeightedRank
FROM qryCustomerTotals;

The difference is (values modified to show effect):
ID CustomerTotal WeightedRank
3 100 0
1 571 2.14
2 1100 4.55
4 1200 5
 
Let me try and explain the reasoning aound the question, and see if that helps.
Say I have a list of 10 thousand customers. Some of them pay 10 days late some 45 days late and some 15 days early or maybe even 27 days early.

So what I would like to have happen is that access would look at all my customers and average them from latest payment to earliest payment and then rank them by how they paid. The scale I would like to use is 0.0- 5.0, with 5.0 being the best. Now if all of my customers paid early, the top 10% would either get a 5.0 – 4.0. Depending on who paid first out of the early payers, the same can be done with late payers, but I would like them all to be in the same list.

So it would look something like this
The top 10 good paying customers would get a 5.0
The next ten would get a 4.9
The next ten would get a 4.8
Going all the way down the worst paying customer would get a 0.0
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top