Add Row Number in Query

[Jeremiah32]

I seen this before, but I can't remember how it was done. I googled the topic but I'm not having much luck. I'm looking for a formula that would give the row number in a query. Kinda like autonumber function new creating a table.

 

[PHV]

Keywords for search: rank or ranking.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya Jeremiah32 . . .

Can't figure why your look for row numbers when [blue]as programmers[/blue] we would be looking for [blue]Primary Keys![/blue] . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[TheAceMan1]

Jeremiah32 . . .

Hit submit too soon! . . .

If you want to rank the records yourself (can't see why) try the following: . . .
[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: