Ranking Query

automationamateur

Board Regular
Joined
Jan 3, 2011
Messages
164
I have a simple query that results in two fields: 1. Client#, and 2. Margin$. Query1 (lets call it) is sorted Descending on Margin$. I simply want to add sequential #'s based on Margin$ (see below, 'rank' column is what I'm after. I am shocked by how difficult this is. I have tried multiple suggestions from AllenBrowne, datapigtechnologies, microsoft, other blogs, etc. My query has ~150 records (each record of a unique clientid and associated margin $'s). The queries I've tried don't fail but I've waited 10+ minutes waiting for it to complete and it never does. Are ranking queries, dsums, dlookups, etc all really this slow given I only have 150 records to rank? I used the exact query at the link below. Am I missing something really obvious? Thanks for the help guys!


Client#Margin $Rank
101 $ 1,000.001
102 $ 900.002
103 $ 800.003
104 $ 700.004
105 $ 600.005

<colgroup><col><col><col></colgroup><tbody>
</tbody>
Code:
http://support.microsoft.com/kb/208946

Code:
Seniority: (Select Count(*) from Employees Where [HireDate] < _    [Emp1].[HireDate];)

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Did you use the expression like this:
Code:
Seniority: (Select Count(*) from Employees Where [HireDate] < _    [Emp1].[HireDate];)
or like this:
Code:
Seniority: (Select Count(*) from Employees Where [HireDate] < [Emp1].[HireDate];)

The second option is the one to use; the underscore is just a line continuation symbol and you don't need it here.

If the query is sorted the subquery expression should run quite quickly. No ides why it takes so long in your case, unless you are summarising large amounts of data in a query and applying the subquery at the same time.
In that case, try:
Create the summary query (the one you call Query1)
Now create Query2, using the fields from Query1 and adding the ranking.

Denis
</pre>
 

automationamateur

Board Regular
Joined
Jan 3, 2011
Messages
164
Denis,

Sorry, I should have been more specific. I used code like your second example. I didn't use the underscore that was just a copy paste from the Microsoft website. I agree with your suggestion. My "Query 1" is a summary of a lot of data and is in descending order (do I need a "sort" above and beyond descending order on Margin $???) but it itself takes maybe 20 seconds to run which I am satisfied with. Then simply using Query 1 as the source when I try to run the 'ranking query' it just hangs forever. Margin$ is a calculated field but I figured since it is calculated in query 1 it will just bring that in as a value in the 'ranking query'. Does the ranking query have to be part of an aggregate query for it to work? Just throwing out possible ideas on what I'm doing wrong. Thanks for the help thus far.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
If chaining the queries doesn't speed things up (and sometimes it doesn't, because one or more of the queries is complex) my next line of attack is to use a make-table query to convert the first big query to a table, then perform subsequent operations on that table.
So... turn Query1 into a make-table query: call the output table something like tmakSummary.
Base Query2 on tmakSummary.

To simplify the processing, create some code to run both in sequence and assign the routine to a button on a form. Something like...
Code:
Sub RunSummary()
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "Query1"
  DoCmd.OpenQuery "Query2"
  DoCmd.SetWarnings True
End Sub

Denis
 

automationamateur

Board Regular
Joined
Jan 3, 2011
Messages
164
I just now got around to looking at this again and your suggestion worked! I made my query 1 a 'make table', then ran a ranking query off temp table which produces correct results in about 5 seconds. Thanks so much.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top