New Tips Added Weekly!
Click here to get on our Mailing List

Row Number in a Query

Adding a Row Number in your Query.

In this tutorial you will learn how to add a ROW number to a Query.

Take an example Table of some Names.

You wish to add the Row number beside them:

There is no row_number like in SQL Server.

You can achive this in a number of ways:

	RowNum: DCount("[ID]","[tblNames]","[ID]<=" & [ID])
	

See this FREE Tip on DCOUNT.

	SELECT A.*, 
		(SELECT COUNT(*) FROM tblNames WHERE A.ID>=ID) AS RowNum
	FROM tblNames AS A
	ORDER BY A.ID;
	

These are not very efficient so don't use them on large recordsets.

An addition to this could be adding the Ordinal or Suffix of ^st, ^nd, ^rd or ^th

Just create a Function:

	Function myOrdinal (D as Integer) as String
		Dim Suffix as String
		Select Case D
			Case 1, 21, 31: Suffix = "st"
			Case 2, 22: Suffix = "nd"
			Case 3, 23: Suffix = "rd"
			Case Else: Suffix = "th"
		End Select
		myOrdinal = D & Suffix
	End Function
	

First shown at Forum.

By: Alex Hedley

Click here to sign up for more FREE tips