Ranking in query

[tinmar]

I have tried to read and 'understand' other posts on the same question, but ... way over my head. I have a query which from some tables pulls revenue and then i have added the 'descending' in the sort, however what i want to know is how to add another field which provides the ranking within the query and i cant get my head around it.....

Can someone please help

 

[Andrzejek]

Some little things, like your SQL and a sample of its outcome would be nice to see....

Have fun.

---- Andy

 

[TheAceMan1]

How are ya tinmar . . .

Have a look here: How to Rank Records Within a Query

I also have one of my own:
[ol][li]Open your query in [blue]query design view[/blue].[/li]
[li]Open the [blue]properties window.[/blue][/li]
[li]Click the [blue]header[/blue] of the [blue]field list[/blue]. The properties window should show the [purple]Alias Property[/purple] with your [blue]current table name[/blue]. [purple]Add[/purple] numeral [purple]1[/purple] to the end of it. If your table name is [purple]tblPoints[/purple] then the alias should be [purple]tblPoints1[/purple].[/li]
[li]Copy/paste the appropriate [blue]ascending/decending[/blue] code line below to a [blue]blank column on the Field row[/blue]. ([blue]you[/blue] substitute the proper names in [purple]purple[/purple]):

[blue]Note: [blue][b]FN[/b][/blue] is FieldName 
      [blue][b]TN[/b][/blue] is TableName
      [blue][b]TN1[/b][/blue] is Alias TableName
      [blue][b]CFN[/b][/blue] is CustomFieldName

[b][u]Ascending[/u][/b]
[purple][b]CFN[/b][/purple]:(Select Count(*) from [purple][b]TN[/b][/purple] Where [[purple][b]FN[/b][/purple]] < [[purple][b]TN1[/b][/purple]].[[purple][b]FN[/b][/purple]];)

[b][u]Decending[/u][/b]
[purple][b]CFN[/b][/purple]:DMax("[[purple][b]FN[/b][/purple]]","[purple][b]TN[/b][/purple]")-(Select Count(*) from [purple][b]TN[/b][/purple] Where [[purple][b]FN[/b][/purple]] < [[purple][b]TN1[/b][/purple]].[[purple][b]FN[/b][/purple]];)[/blue]

[/li][/ol]

[purple]Thats it . . . . give it a whirl & let me know . . . .[/purple]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!