Help Speedup Rank Query

[TJVFree]

I’m running ms access from my workstation (PC) and running queries on my computer. I’m trying to speed up my query particularly this one (see below)

Once my testing has been completed, I was planning on reconnecting through ODBC. Should I not be doing this, and is there a more efficient way to run my Ranking query?

Thanks for all your help Jeff

Corey

 SELECT qryCustomerTotals1.OMNI_Number, qryCustomerTotals1.Account_Executive_Name, qryCustomerTotals1.[Correspondent Name], qryCustomerTotals1.CustomerTotal, (Select count(*) from qryCustomerTotals1 as B where qryCustomerTotals1.CustomerTotal > B.customerTotal) AS Rank, (Select count(*) from qryCustomerTotals1) AS TotalRecords, [Rank]/([TotalRecords]-1)*5 AS 0to5Rank
FROM qryCustomerTotals1
ORDER BY qryCustomerTotals1.OMNI_Number, qryCustomerTotals1.Account_Executive_Name, qryCustomerTotals1.[Correspondent Name], qryCustomerTotals1.CustomerTotal DESC;

 

[markros]

What is your SQL Server version? In SQL 2005 and up take a look into windowing functions such as DENSE_RANK() and RANK().

PluralSight Learning Library

 

[TJVFree]

I'm not sure about the server but I'm using ms access 2003

 

[markros]

If you're using Access database, you may get better help in Access forum. I think for Access it's unlikely you can invent a better query than the one you're using.

PluralSight Learning Library

 

[TJVFree]

Thank you for your time