Create grouped counter column in query

[Gerard1]

I would like to create a counter column in a table/query that resets to 1 upon a change in a particular field.
For example:
PersonID Counter
100 1
100 2
100 3
200 1
200 2
300 1
400 1

Thanks in advance for help!

 

[BlackKnight]

Hi,

Using a form and in the control that represents the PersonID field you enter code into its AFTER_UPDATE event.

Private Sub PersonID_AfterUpdate()
Me!Counter = 1
End Sub

Have a good one!
BK

 

[Gerard1]

Thanks,
but is there a way to create a table or query with all these values without using forms or reports?
Thanks again!!

 

[gol4]

This is possible using a query if you have either an autonumber type index for the table or a date field

using index
select tbl.personid,(select count(a.personid) from tbl as a where a.personid = tbl.personid and a.index <= tbl.index)as counter
from tbl

using date
select tbl.personid,(select count(a.personid) from tbl as a where a.personid = tbl.personid and a.dtfld <= tbl.dtfld)as counter
from tbl

or you could create a function that would do it.

 

[Gerard1]

I tried the 1st method and it works.
Thanks a million!