Rank field in a query 1

[MikeAngelastro]

Hi,<br>I would like to include a rank field in a query that sorts descending. I believe the row number would be equal to the rank. For instance, in a query listing in descending order of revenue generated the top 10 sales people, the sales person at the top of the list would be ranked 1, the next would be ranked 2, etc. Does anyone know how to create a field that would include the row number to be used as the rank? The result could be used on a form. <br><br>I know how to get the same result in a report by creating a text box equal to 1 and making it a running sum overall. But is there any way to do this in a query?<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Thanks,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Mike

 

[kathryn]

Mike,<br><br>The only way I can see to do this using a query is to use an append query and append your query results to a table that has a Autonumber field.&nbsp;&nbsp;You would have to create this table every time you needed it, to insure that you get an initial Autonumber value of 1.<br><br>Have you considered using the query recordset as the basis for a form, and have code behind the form that gives the recordset's AbsolutePosition property as the Rank number?&nbsp;&nbsp;That should work.<br><br>Let me know if you need more explanation or assistance.<br><br>Kathryn

 

[MikeAngelastro]

Kathryn,<br><br>Thanks for your help.&nbsp;&nbsp;I am aware of the ways you had suggested.&nbsp;&nbsp;I was merely wondering if there was a way to do it directly&nbsp;&nbsp;- in a query.&nbsp;&nbsp;I was thinking that, since the row number is available running a SQL statement in code, perhaps it was also available as the original recordset was being generated.&nbsp;&nbsp;This would preclude the need for a second query.<br><br>Mike<br><br>

 

[Cornerstone]

I read this on another web site. It sounds like what you want to do--<br><br>Your application may need to produce a report or query which has each row of output numbered to enhance report readability or to print only every X row of output. You can easily create a query which has an auto number assigned field for each row of output by using a subquery. <br>A subquery is a query which is imbeded within your target query or report recordsource. They are often used to limit data to a subset of a standard recordset, find dublicate records or another action. To create an auto number field, we use a subquery as a field in our query to count the number of records which occured in the output of the target query before the current row. <br><br>Critical to creating the auto number subquery is basing the subquery a virtual copy of a unique index of the main query's recordset and include the same parameters for output that the main query does. As an example in an order's report, usually the order number would be a unique field in the target query or recordset. <br><br>If you were listing all orders in the table, &quot;tblOrders&quot; and wanted to auto number the output rows of such an order query, you would enter the following in an output field of the orders query: <br><br>RowNum: (Select Count (*) FROM [tblOrders] as Temp <br>WHERE [Temp].[OrdNum] &lt; [tblOrders].[OrdNum])+1<br>If you need to constrain your query's output (as is usually the case,) to a specific set of records, you must add the same constraints to your auto number subquery, so that the temporary virtual recordset generated by the subquery has the same result set as your main query. So if as an example you wanted the output or target query to only list orders between a specific &quot;StartDate&quot; and &quot;EndDate&quot; you would also add the same parameters to the subquery previously shown, so that your subquery in the field would now read: <br>RowNum: (Select Count (*) FROM [tblOrders] as Temp <br>WHERE ((Temp.[OrdDate] BETWEEN [Startdate] AND [EndDate] ) <br>AND ([Temp].[OrdNum] &lt; [tblOrders].[OrdNum])))+1<br>This technique would work on any unique index contained in your main query be it either a numbered index, or alpha index. <br>

 

[dkillilea]

I tried Cornerstone's idea and can't get it to work. I typed the info in a field just like he has it, brought in my table, and now the query doesn't do anything when I click run.

I've read at least 10 other ideas for getting the row number inside a query. Nothing seems quite as easy as this one. I know very little about SQL so I always try and use the fields inside Access Queries to do stuff like this.

The query I am trying to autonumber is called (don't laugh, I know it's long):
"Beginning Stock Status - Sort Order Tier 003"

And I didn't have a unique order number like in the example so I used the concatentate function to combine all the fields necessary to have one. I labeled this field "Unique Value"

 

[Golom]

The "standard" (if there is such a thing) form for a rank query is

Select fld1,

       (Select Count(*) From tbl X
        WHERE X.fld1 <= T.fld1) As Rank

From Tbl T

Order By fld1

This is for an ASC sort. If you want DESC then change "<=" to ">=" in the subquery and add "DESC" to the Order By clause.

You will get duplicate ranks for duplicated values of "fld1". For example, this would produce
[tt]
fld1 Rank
1 1
2 2
2 2
3 4
4 5
4 5
[/tt]