Sequence Number reset at each ID exchange

[adgjqetuo]

I have two tables - one with parent Records, the other with child records. The ID links the two tables together.

I want to add a sequence number on the child table which resets at each ID change. What would be the best way to accomplish this?

PC_ID       ID    Name        Address

P           1     Parent1     Address1        
P           2     Parent2     Address1        





PC_ID       ID    Name        Address         [B]SEQ[/B]

C           1     Child1      Address1        1
C           1     Child2      Address1        2
C           1     Child3      Address1        3
C           1     Child3      Address1        4
C           2     Child1      Address1        1
C           2     Child2      Address1        2
C           2     Child3      Address1        3

 

[llkhoutx]

Add an AutoNumber column to the 2nd table.

 

[adgjqetuo]

How will that solve my problem? The sequence needs to reset at each ID number. Looking at my sample data above - ID 1 has 1-4 and ID 2 has 1-3, etc.

 

[llkhoutx]

OK, build a function to see if ID changed, if so reset the counter to 1, otherwise increment it.

 

[adgjqetuo]

That would be great. Can someone here help me with that or should I reach out to the function / module section?