Free Lessons Courses Seminars TechHelp Fast Tips Templates Topic Index Forum ABCD

 Home   Courses   Seminars   Templates   TechHelp   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum ------------ Excel Courses Excel Index ------------ Code Vault Testimonials

 Tips & Tricks 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.

tblNames
IDFirstName
1Fred
3Barry
4Joan
5Ben
7Will

You wish to add the Row number beside them:

tblNames
Row #IDFirstName
11Fred
23Barry
34Joan
45Ben
57Will

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.

 Check out these other pages that may be of interest to you:
 5/26/2023 Hide With Macro 5/25/2023 Calculator 6 5/24/2023 Object Size Analyzer 5/24/2023 Calculator 5 5/23/2023 Plural 5/22/2023 Calculator 4 5/19/2023 Calculator 3 5/18/2023 Calculator 2 5/17/2023 Calculator 5/16/2023 Variable Default