Access SQL - Counter Field

In SQL for Access, how can I make a counter field so that the counter restarts as the ID field changes per number of rows. Please note the following example with the fields:

1.[Counter] - this field is created from fields 2. and 3.
2. [ID] - member ID #
3. Row - this is the number of rows per ID that can range from 1 - limitess - not a field

The original is:

ID      
1
1
1
1
1
1
2
2
2
2
45
45
68
68
68
68
68
68
68
68
68
68
68
68

The newly created output table will be ([Counter] has been newly created):

ID      Counter
1          1
1          2
1          3
1          4
1          5
1          6
2          1
2          2
2          3
2          4
45        1
45        2
68        1
68        2
68        3
68        4
68        5
68        6
68        7
68        8
68        9
68        10
68         11
68          12

It is preferred that there is a SQL based solution (not VB).

Thanks.