Row_Number () in an access select statement

Question

I believe that similar questions have been asked, but I cannot find a solution that works for me.

I have a database that I use to sort digitized books and their pages, and I'm trying to sort several thousand pages containing maps. Of the two tables, I use the first lists of all pages in the book and the order they have in the book, he received three columns (bookID, pageOrder, pageID), each page has its own row. The second table lists all the places (on the map) that are found on each page, it has two columns (pageID, placeID), if there are several places on one page, and then a new table is added to the table for each place.

What I need to do is create a select statement that gives each combination the page identifier ID / placeID a unique number, but the numbers should go in the order in which they appear in the book. In SQL Server, I would do the following:

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 

Sorry, I'm stuck using Access. Ideally, I would like to do this (if possible) using 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 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. Harder 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 suggests that the combination of bookId , pageOrder , placeId` is unique.

Answer 2

I know this is an old question, but it was the best search, and I have not seen any other solutions on the Internet, so I hope this helps others.

My solution works for any dataset, whether it has a unique identifier or not.

Add the following VBA code to the 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 is a sample request:

 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 input for RowNum and GetRowNum . It is important to note that use RowNum only the first time you want a line number, and use GetRowNum every time after. This is done so that one row does not increase the counter.

The last thing you need to do is create a macro that runs ResetRowNum and run it after each request that you use with this method, or if you are running a series of requests through a macro or VBA, be sure to run ResetRowNum after every request that uses these functions.

Also, avoid presenting a data table, since it seems to constantly recalculate formulas when scrolling, which leads to a constant increase in the number.

Answer 3

Request for sorting 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