Incrementing Values based on value of another field

[bsc gmcc]

I have a table with the typical id column that is auto number. I need to have
another field that increments by the value of the state column. In my
example, it is the 3rd column that I want to increment based on the state
that was chosen in column 2. I was trying to store the last count used for
that state in aother table and incrementing it when a record was inserted but
I am not getting reliable results. Sometimes it will increment sometimes it
won't. Is there a common and best practice approach for doing this? Thanks.

1 NY 1
2 NY 2
3 VA 1
4 VA 2
5 VA 3

 

[Marshall Barton]

I have a table with the typical id column that is auto number. I need to have
another field that increments by the value of the state column. In my
example, it is the 3rd column that I want to increment based on the state
that was chosen in column 2. I was trying to store the last count used for
that state in aother table and incrementing it when a record was inserted but
I am not getting reliable results. Sometimes it will increment sometimes it
won't. Is there a common and best practice approach for doing this? Thanks.

1 NY 1
2 NY 2
3 VA 1
4 VA 2
5 VA 3

An exxtra table is not really needed for this.

THe usual approach is to use code in the form's BeforeUpdate
event to calculate the next avialable number:

Me.[the second column] = DMax("[the second column]". _
"the table", "state='" & Me.state & "' "