PC Review


Reply
Thread Tools Rate Thread

Help with Ranking SQL/VBA/Macros/Modules

 
 
TCB TCB is offline
New Member
Join Date: Dec 2010
Posts: 2
 
      17th Dec 2010
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.



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




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




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








Quote:
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
Quote:

FROM GP1_Master_Table_Count_Apps
;



Thank you for taking helping me with this question
Attached Files
File Type: zip Test.zip (614.0 KB, 17 views)
 
Reply With Quote
 
 
 
 
V_R V_R is offline
@120Hz
V_R's Avatar
Join Date: Jan 2005
Location: 127.0.0.1
Posts: 10,466
 
      18th Dec 2010
That last code box, i changed to quote as it totally messed up the forums layout..

 


Gigabyte Z87X-OC | i7 4770K @ 4.4Ghz | Corsair H80i | 16Gb TeamGroup Vulcan Orange
EVGA 780 FTW | Creative X-FI Titanium HD | 500Gb Samsung EVO SSD + 4TB's of Storage
Corsair HX850 | Logitech G25 | Ducky Shine III | Deathadder 2013 | Wireless 360 Pad
Corsair Carbide 300R | BenQ XL2420T | Acoustic Energy Aego M | Windows 8.1 64 Bit
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Ranking (Look for previous ranking) wilkins Microsoft Access Queries 3 11th Jan 2012 01:22 PM
Need Help on Ranking computerteckie Microsoft Excel Misc 5 4th Jun 2004 04:02 PM
Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! -$- Windows XP Internet Explorer 2 21st Dec 2003 11:45 PM
Ranking Dates in a Query Help Lou Microsoft Access Queries 4 6th Nov 2003 03:42 PM
Website ranking issue - Help. Johnny Microsoft Frontpage 2 4th Sep 2003 11:05 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:21 PM.