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

Adding a sequence/set number to a table

P: 1
Is there a simple way to add a column to an existing table & populate it with consecutive numbers ?

I have a table which I want to add a new column to, and populate on each row a consecutive number - eg 1,2,3,4,5,6

thanks !
Dec 13 '06 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 10K+
P: 14,534
If this is a one off then there is no need for code.

Use the following steps.

Add two new columns in the table design. Set one to Number and one to AutoNumber.

Run 'compact and repair' to reset the autonumber.

Then open the table and make sure the records have consecutive numbers in the auto number column.

You can then run an update query to copy the number to the other column.

Expand|Select|Wrap|Line Numbers
  1. UPDATE TableName SET NumberColumnName=AutoNumberColumnName;
Then delete the Autonumber column.

Dec 13 '06 #2

Expert Mod 15k+
P: 31,494
Neat trick :).
Dec 14 '06 #3

P: 1
I used this to create a sequence number starting from 1, for each batch of journals..

Expand|Select|Wrap|Line Numbers
  1. Function lngSequenceNumber(lngKey As Long) As Long
  2.     Static lngKeepKey As Long
  3.     Static lngKeepSeq As Long
  5.     If lngKey <> lngKeepKey Then
  6.         lngKeepKey = lngKey
  7.         lngKeepSeq = 1
  8.     Else
  9.         lngKeepSeq = lngKeepSeq + 1
  10.     End If
  12.     lngSequenceNumber = lngKeepSeq
  13. End Function
Expand|Select|Wrap|Line Numbers
  1. SET PP.JournalSeqID = lngSequenceNumber (PP.JournalBatchID);
Jun 18 '15 #4

Post your reply

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