By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,281 Members | 2,201 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,281 IT Pros & Developers. It's quick & easy.

Incremental sequence number based on data values

P: 1
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
Jul 30 '08 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,661
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.
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblTest3 AS tTO
  2.    SET OrdinalVal=(SELECT Max(Nz([OrdinalVal],0))+1
  3.                    FROM tblTest3 AS tTI
  4.                    WHERE tTI.FirstVal=tTO.FirstVal)
  5. 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.
Aug 5 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.