Row_Number() in Access select statement

Question

I believe similar questions have been asked but I can't quite find a solution that works for me.

I've got a database that I use to sort through digitised books and their pages and I'm trying to sort through several thousand pages that contain maps. Of the two tables I'm using the first lists all the pages in a book and the order they occur in the book, it's got three columns (bookID, pageOrder, pageID), each page has its own row. The second table lists all the places (in a map) that occur on each page, it has two columns (pageID, placeID) if there are multiple places on one page then a new row is added to the table for each place.

What I need to do is create a select statement that gives every pageID/placeID combination a unique number but the numbers must go in the order they appear in the book. In SQL Server I would do this:

SELECT ROW_NUMBER() OVER(ORDER BY bp.bookID, bp.pageOrder, pp.placeID) AS uniqueNumber, pp.pageID, pp.placeID
FROM booksAndPages AS bp INNER JOIN pagesAndPlaces AS pp ON bp.pageID = pp.pageID

Unfortunately, I'm stuck using Access. Ideally I'd like to do it (if possible) with a single SQL statement, similar to the one above but I would also try it using VBA.

Any help is greatly appreciated.

Answer 1

This is the query that you want:

SELECT ROW_NUMBER() OVER (ORDER BY bp.bookID, bp.pageOrder, pp.placeID) AS uniqueNumber,
       pp.pageID, pp.placeID
FROM booksAndPages AS bp INNER JOIN
     pagesAndPlaces AS pp
     ON bp.pageID = pp.pageID;

You can get the same result using a correlated subquery. More complicated and more expensive, but possible:

SELECT (select count(*)
        from booksAndPages AS bp2 INNER JOIN
             pagesAndPlaces AS pp2
             ON bp2.pageID = pp2.pageID
        where bp2.bookID < bp.bookID or
              (bp2.bookID = bp.bookID and bp2.pageOrder < bp.pageOrder) or
              (bp2.bookID = bp.bookID and bp2.pageOrder = bp.pageOrder and
               bp2.placeId <= pp.PlaceId
              )
       ) as uniqueNumber,
       pp.pageID, pp.placeID
FROM booksAndPages AS bp INNER JOIN
     pagesAndPlaces AS pp
     ON bp.pageID = pp.pageID;

This assumes that the combination bookId, pageOrder, placeId` is unique.

Answer 2

I know this is an old question, but this was a top search and I haven't seen any other solutions on the internet so I hope this will help others.

My solution works for any dataset regardless of if it has a unique identifier or not.

Add the following VBA code into a module:

Public row as Variant

Function RowNum(dummy) As Integer
    row = row + 1
    RowNum = row
End Function

Function GetRowNum(dummy) As Integer
    GetRowNum = row
End Function

Function ResetRowNum()
    row = 0
End Function

Now here's a sample query:

SELECT Table1.Field1, Table1.Field2, RowNum([Field1]) AS RowId, 
    "Row: "&GetRowNum([Field1]) AS RowText
FROM Table1

You can add any 'ORDER BY' or even 'GROUP BY' if you wish. You can use any field that will be in the query output as the input for RowNum and GetRowNum. Important to note is to only use RowNum for the first time you want the row number and use GetRowNum every time after. This is to prevent one row increasing the counter more than once.

The last thing you need to do is create a macro that runs ResetRowNum and run it after every query you use with this method, or if you're running a series of queries through a macro or VBA, make sure to run ResetRowNum after every query that uses these functions.

Also avoid datasheet view, as it seems to constantly recalculate the formulas when you scroll, making the numbers steadily increase.

Answer 3

Query to sort and/or group

SELECT Table1.Field1, 
       Table1.SomeDate, 
       Table1.Field2,
       RowNumber([Field1]) AS RowId,
       "Row: " & GetRowNum([Field1]) AS RowText
FROM Table1
ORDER BY Table1.Field1, Table1.SomeDate;

---

Field1  Field2  RowId   RowText
James   2   1   Row: 1
James   35  2   Row: 2
James   6   3   Row: 3
James   86  4   Row: 4
James   67  5   Row: 5
James   35  6   Row: 6
Maria   4   1   Row: 1
Maria   54  2   Row: 2
Samuel  46  1   Row: 1
Samuel  32  2   Row: 2
Samuel  7   3   Row: 3
Thomas  43  1   Row: 1
Thomas  65  2   Row: 2
Thomas  5   3   Row: 3

---

Public StoredRowNumber As Variant
Public OldlastField As Variant

Function RowNumber(TheField) As Integer
    If OldlastField = TheField Then
        'nada
    Else
        ResetRowNum
    End If
    StoredRowNumber = StoredRowNumber + 1
    RowNumber = StoredRowNumber
    OldlastField = TheField
End Function

Function GetRowNum(TheField) As Integer
    GetRowNum = StoredRowNumber
End Function

Function ResetRowNum()
    StoredRowNumber = 0
    'OldFieldItem = Null
End Function