Ranking Query

automationamateur

Board Regular
Joined
Jan 3, 2011
Messages
166
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>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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
Back
Top