Help with Ranking SQL/VBA/Macros/Modules

[TCB]

I’m looking for help on being able to speed up my Ranking query. Below are a couple of my Rank formulas. And way below is what I copied from access sql.

How would I clean it up so my query will run faster?

How would I make this a actually sql statement, so that it will run faster?



Or should I look at doing a macro or modules?



I have also attached the database that uses these queries to help better understand what I'm trying to do. Please look at query titled:” qryRank_and_WeightedRank”



If anyone has any advice help or recommendations, please don’t hesitant to tell me. I'm still new and learning and any time I get help or advice it helps me learn.

3MonthRank: (Select count(*) from GP1_Master_Table_Count_Apps as B where[/size][/font]
  
  [font=Times New Roman][size=3]GP1_Master_Table_Count_Apps.[3Month_App_Count] > B.[3Month_App_Count])

6MonthRank: (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[6Month_App_Count] > B.[6Month_App_Count])

9MonthRank: (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[9Month_App_Count] > B.[9Month_App_Count])

SELECT GP1_Master_Table_Count_Apps.OMNI_Number, GP1_Master_Table_Count_Apps.[3Month_App_Count], (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[3Month_App_Count] > B.[3Month_App_Count]) AS 3MonthRank, (Select count(*) from GP1_Master_Table_Count_Apps) AS TotalRecords, [3MonthRank]/([TotalRecords]-1)*5 AS 3Month_0to5Rank, CInt([3Month_0to5Rank]*10)/10 AS 3Month_Star_Rating, (Select sum([3Month_App_Count])from GP1_Master_Table_Count_Apps) AS 3MonthGrandTotal, (select min([3Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 3MonthMinTotal, (select max([3Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 3MonthMaxTotal, 5*(([3Month_App_Count]-[3MonthMinTotal])/([3MonthMaxTotal]-[3MonthMinTotal])) AS 3MonthWeightedRank, GP1_Master_Table_Count_Apps.[6Month_App_Count], (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[6Month_App_Count] > B.[6Month_App_Count]) AS 6MonthRank, [6MonthRank]/([TotalRecords]-1)*5 AS 6Month_0to5Rank, CInt([6Month_0to5Rank]*10)/10 AS 6Month_Star_Rating, (Select sum([6Month_App_Count])from GP1_Master_Table_Count_Apps) AS 6MonthGrandTotal, (select min([6Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 6MonthMinTotal, (select max([6Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 6MonthMaxTotal, 5*(([6Month_App_Count]-[6MonthMinTotal])/([6MonthMaxTotal]-[6MonthMinTotal])) AS 6MonthWeightedRank, GP1_Master_Table_Count_Apps.[9Month_App_Count], (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[9Month_App_Count] > B.[9Month_App_Count]) AS 9MonthRank, [9MonthRank]/([TotalRecords]-1)*5 AS 9Month_0to5Rank, CInt([9Month_0to5Rank]*10)/10 AS 9Month_Star_Rating, (Select sum([9Month_App_Count])from GP1_Master_Table_Count_Apps) AS 9MonthGrandTotal, (select min([9Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 9MonthMinTotal, (select max([9Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 9MonthMaxTotal, 5*(([9Month_App_Count]-[9MonthMinTotal])/([9MonthMaxTotal]-[9MonthMinTotal])) AS 9MonthWeightedRank, GP1_Master_Table_Count_Apps.[12Month_App_Count], (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[12Month_App_Count] > B.[12Month_App_Count]) AS 12MonthRank, [12MonthRank]/([TotalRecords]-1)*5 AS 12Month_0to5Rank, CInt([12Month_0to5Rank]*10)/10 AS 12Month_Star_Rating, (Select sum([12Month_App_Count])from GP1_Master_Table_Count_Apps) AS 12MonthGrandTotal, (select min([12Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 12MonthMinTotal, (select max([12Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 12MonthMaxTotal, 5*(([12Month_App_Count]-[12MonthMinTotal])/([12MonthMaxTotal]-[12MonthMinTotal])) AS 12MonthWeightedRank

FROM GP1_Master_Table_Count_Apps

;



Thank you for taking helping me with this question

 

[V_R]

That last code box, i changed to quote as it totally messed up the forums layout..