Incremental sequence number based on data values

[kama]

I want to create sequence number for repeating data values. This sequence number will re-start from 1 for each new value. Example as below:-

Amount sequence_number
200 1
200 2
100 1
500 1
500 2
500 3
500 4

[NeoPa]

Well, you haven't shared much information with us so I will have to make up some of the details here.

Assuming then, you have a table [tblTest3] with two numeric fields [FirstVal] and [OrdinalVal].

In a fully mature RDBMS the following SQL could probably work, but due to limitations within Access this is not supported.

UPDATE tblTest3 AS tTO
   SET OrdinalVal=(SELECT Max(Nz([OrdinalVal],0))+1
                   FROM tblTest3 AS tTI
                   WHERE tTI.FirstVal=tTO.FirstVal)
WHERE [OrdinalVal] Is Null

In view of this I can only suggest you use some VBA code to process through the table in order of [FirstVal]. Simply incrementing as you go.