How to setup access to Rank

[TJVFree]

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)

 

[RMcCallan]

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

 

[RMcCallan]

right click*

Thanks,
Rebecca

 

[TJVFree]

Yes i can do that, but I'm trying to do a ranking from 0-5

 

[dhookom]

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

 

[RMcCallan]

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

 

[RMcCallan]

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

 

[TJVFree]

In a table

 

[RMcCallan]

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

 

[TheAceMan1]

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]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[RMcCallan]

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

 

[dhookom]

As TheAceMan1 and I have asked...somebody needs to define "put them in order".

Duane
Hook'D on Access
MS Access MVP

 

[RMcCallan]

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

 

[dhookom]

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

 

[RMcCallan]

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

 

[dhookom]

I would expect to see some type of expression that involves table and field names like:

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

 

[RMcCallan]

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

 

[MajP]

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

SELECT 
 tblOne.ID, 
 [3M]+[6M]+[9m]+[12M] AS CustomerTotal
FROM 
 tblOne
ORDER BY 
 [3M]+[6M]+[9m]+[12M];

Then

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:

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

 

[MajP]

That is a linear ranking from 1 to 5. More likely you want a weighted ranking.

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

 

[TJVFree]

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