Need update query help

[ssmith001]

I have a table that has 3 columns (ID, STOP_NUM, NUM_STOPS). Every time I insert a record for an ID that already exists, I need to update the NUM_STOPS field to the total number of records for the given ID. For example:

ID         STOP_NUM        NUM_STOPS

1001            1             1

After inserting a new record, it should look like this:

ID         STOP_NUM        NUM_STOPS

1001            1              2
1001            2              2

I've tried to write this as a query but I keep getting the "query is not an updateable query" message.

 

[pbaldy]

The normalized solution would be to simply count how many 1001 records there are, not store the number in every record.

 

[ssmith001]

there are many other columns on this table, but I just mentioned 3 of them to illustrate my point.

 

[pbaldy]

I don't see how it matters how many columns there are. The point is, why store a number that can be so easily calculated, especially since you have to constantly update it?