Formula for a Rolling Scale 1-5 Help

[TJVFree]

I'm trying to get I think it’s called a rolling scale.
I have 500 records but I would like the rolling scale to rate them from 1-5 with 1 being the best.
The formula I thought would work was "3Month_0to5Rank: [3MonthRank]/([Past_3_Month#]-1)*5" but turns out if keeps going after 5 or I tried switching the formula it gives me more weird numbers.

I really appreciate you taking the time for assist me with this problem.

The formula below worked for a different query but now it is giving me from 0.00125 to 1253.21500

3Month_0to5Rank: [3MonthRank]/([TotalRecords]-1)*5

[SQL]
SELECT [CUSB_Rank tbl_1].[OMNI#], [CUSB_Rank tbl_1].[TotaRecords$], [CUSB_Rank tbl_1].[TotaRecords#], [CUSB_Rank tbl_1].Jan_Loan_Amount, [CUSB_Rank tbl_1].Jan_App_Count, [CUSB_Rank tbl_1].Feb_Loan_Amount, [CUSB_Rank tbl_1].Feb_App_Count, [CUSB_Rank tbl_1].Mar_Loan_Amount, [CUSB_Rank tbl_1].Mar_App_Count, [CUSB_Rank tbl_1].Apr_Loan_Amount, [CUSB_Rank tbl_1].Apr_App_Count, [CUSB_Rank tbl_1].May_Loan_Amount, [CUSB_Rank tbl_1].May_App_Count, [CUSB_Rank tbl_1].Jun_Loan_Amount, [CUSB_Rank tbl_1].Jun_App_Count, [CUSB_Rank tbl_1].Jul_Loan_Amount, [CUSB_Rank tbl_1].Jul_App_Count, [CUSB_Rank tbl_1].Aug_Loan_Amount, [CUSB_Rank tbl_1].Aug_App_Count, [CUSB_Rank tbl_1].Sept_Loan_Amount, [CUSB_Rank tbl_1].Sept_App_Count, [CUSB_Rank tbl_1].Oct_Loan_Amount, [CUSB_Rank tbl_1].Oct_App_Count, [CUSB_Rank tbl_1].Nov_Loan_Amount, [CUSB_Rank tbl_1].Nov_App_Count, [CUSB_Rank tbl_1].Dec_Loan_Amount, [CUSB_Rank tbl_1].Dec_App_Count, [CUSB_Rank tbl_1].First_3_Month_Loan_Amount, [CUSB_Rank tbl_1].First_3_Month_App_Count, [CUSB_Rank tbl_1].First_6_Month_Loan_Amount, [CUSB_Rank tbl_1].First_6_Month_App_Count, [CUSB_Rank tbl_1].First_9_Month_Loan_Amount, [CUSB_Rank tbl_1].[9Month_App_Count], [CUSB_Rank tbl_1].[12_Month_Loan_Amount_10], [CUSB_Rank tbl_1].[12Month_App_Count_10], [CUSB_Rank tbl_1].First_12_Month_App_Count, [CUSB_Rank tbl_1].[Past_3_Month$], [CUSB_Rank tbl_1].[Past_3_Month#], [CUSB_Rank tbl_1].[Past_6_Month$], [CUSB_Rank tbl_1].[Past_6_Month#], [CUSB_Rank tbl_1].[Past_9_Month$], [CUSB_Rank tbl_1].[Past_9_Month#],

[CUSB_Rank tbl_1].[Past_12_Months$], (Select count(*) from [CUSB_Rank tbl_1] as B where [CUSB_Rank tbl_1].[Past_3_Month#] < B.[Past_3_Month#]+1) AS 3MonthRank, [3MonthRank]/([Past_3_Month#]-1)*5 AS 3Month_0to5Rank INTO CUSB_Master_Table FROM [CUSB_Rank tbl_1];[/sql]

Thanks again
Corey

 

[MajP]

You seem to have 3 posts with different titles, different amount of information, but asking the same question. Don't do this. Just post one thread so that all answers are consolidated, and everyone has complete information. If you need to bring it up in precedence you can just "bump" the thread.

 

[TJVFree]

I'm sorry about that. How do I bump the thread?