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: 
Another way to assign a row number in a query is to use the `DCount` function.

```
SELECT *, DCount("[ID]","[mytable]","[ID]<=" & [ID]) AS row_id
FROM [mytable]
WHERE row_id=15

```
Though this is an old question, this has worked for me, but I've never tested its efficiency...

```
SELECT 
    (SELECT COUNT(t1.SourceID) 
     FROM [SourceTable] t1 
     WHERE t1.SourceID<t2.SourceID) AS RowID, 
    t2.field2, 
    t2.field3, 
    t2.field4, 
    t2.field5
FROM 
    SourceTable AS t2
ORDER BY 
    t2.SourceID;

```

Some advantages of this method:

* It doesn't rely on the order of the table, either - the `RowID` is calculated on its actual value and those that are less than it.
* This method can be applied to any (primary key) type (e.g. `Number`, `String` or `Date`).
* This method is *fairly* SQL agnostic, or requires very little adaptation.

**Notable Disadvantages**

Though this will work with practically any data type, I must emphasise that, for some, it *may* create other problems. For instance, with strings, consider:

| ID | Description | ROWID |
| --- | --- | --- |
| aaa | Aardvark | 1 |
| bbb | Bear | 2 |
| ccc | Canary | 3 |

If I were to insert: `bba Boar`, then the `Canary` `RowID` will change...

| ID | Description | ROWID |
| --- | --- | --- |
| aaa | Aardvark | 1 |
| bbb | Bear | 2 |
| bba | Boar | 3 |
| ccc | Canary | 4 |

You cannot rely on the IDs remaining in place unless the table remains unsorted.