Access SQL - Counter Field

tahirih
tahirih used Ask the Experts™
on
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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Is there another field in the table which indicates the ordering of the new 'Counter' field? (The problem is easier to solve if there is!) ... for example a primary key auto-incrementing field ?

Author

Commented:
At this time no, but it can be created.
 
Please offer your thoughts with a primary key.

Thanks.
Data Engineer
Commented:
If you have a auto-incrementing field like the below attached sample (Prim), then you can try like this.

select *,(select count(*) from Table1 as t2 where t1.ID = t2.ID and t2.Prim<= t1.Prim) as cnt
  from Table1 as t1;
select *,(select count(*) from Table1 as t2 where t1.ID = t2.ID and t2.Prim<= t1.Prim) as cnt
  from Table1 as t1;

Counter	Prim	ID
1	1	1
2	2	1
3	3	1
4	4	1
5	5	1
6	6	1
1	7	2
2	8	2
3	9	2
4	10	2
1	11	45
2	12	45
1	13	68
2	14	68
3	15	68
4	16	68
5	17	68
6	18	68
7	19	68
8	20	68
9	21	68
10	22	68
11	23	68
12	24	68

Open in new window

Author

Commented:
This worked wonderfully. Thank you so much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial