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: 
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.
Thanks for your solutions above! DCount did the trick for me too!

I had to use a combination of date columns and a unique identifier for the sorting portion of it (as well as some additional conditions), so here is what I ended up doing:
 1) I had to check if DateColumnA was null, then check if DateColumnB was null, then use DateColumnC; then, if multiple records have the same date value, they all end up with the same id! 
 2) So, I figured I would use the integer unique ID of the table, and add it up to the time as "minutes". This will always provide different results
 3) Finally, the logic above results in the count starting in 0... so just add 1!

```
SELECT 
 1+DCount("[RequestID]","[Request]","Archived=0 and ProjectPhase <> 2 and iif(isnull(DateColumnA)=true,iif(isnull(DateColumnB)=true,DateColumnC,DateColumnB),DateColumnA)+(RequestID/3600) < #" & if(isnull(DateColumnA)=true,iif(isnull(DateColumnB)=true,DateColumnC,DateColumnB),DateColumnA) + (RequestID/3600) & "#") AS RowID, 
FROM 
 Request
ORDER BY 1

```

I hope this helps you out!