Question: I always use this query in sql server to get Row number in a table:

```
SELECT *
FROM   (SELECT *,
               Row_number()
                 OVER(
                   ORDER BY [myidentitycolumn]) RowID
        FROM   mytable) sub
WHERE  rowid = 15  

```

Now I am working in Access 2010 and this seems to be not working. Is there any replacement for this query in Access?

Answer: 
I needed the best *x* results of points per team.

Ranking does not solves this problem when there are results with equal points.
So I need a `recordnumber`

I made a VBA function in Access to create a `recordnumber` that resets on ID change.

You have to query this query with where `recordnumber <= x` to get the points per team.

NB Access changes the record-number

1. when you query the query filtered on record number
2. when you filter out some results
3. when you change the sort order

That is not what I thought that would happen.

Solved this by using a temporary table and saving the `recordnumbers` and keys or an extra field in the table.

```
SELECT ID, Points, RecordNumberOffId([ID}) AS Recordnumber
FROM Team ORDER BY ID ASC, Points DESC;

```

It uses 3 module level variables to remember between calls

```vb
Dim PreviousID As Long
Dim PreviousRecordNumber As Long
Dim TimeLastID As Date

Public Function RecordNumberOffID(ID As Long) As Long 
'ID is sortgroup identity
'Reset if last call longer dan nn seconds in the past
If Time() - TimeLastID > 0.0003 Then '0,000277778 = 1 second
    PreviousID = 0
    PreviousRecordNumber = 0
End If
If ID <> PreviousID Then
    PreviousRecordNumber = 0
    PreviousID = ID
End If
PreviousRecordNumber = PreviousRecordNumber + 1
RecordNumberOffID = PreviousRecordNumber
TimeLastID = Time()
End Function

```
I might be late. Simply add a new field ID in the table with type AutoNumber. This will generate unique IDs and can utilize in Access too