Help Speeding Up Rank Query

[TCB1]

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;

[olivthill2]

Access does not have the fastest database engine I have ever seen, to say the least, and things get worse when your query is using "attached tables" which is the case here, I guess.

I got a better speed when I replaced my attached table and its ODBC link with a transfer of the query via FTP to the other computer, and another transfer to get the result back. The program is more complex, but it was worthwhile because, the process was working on several thousands of rows. Initially it lasted for about an hour. Then it lasted about 5 minutes.