By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,508 Members | 2,904 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,508 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


MMcCarthy
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;
  2.  
Then delete the Autonumber column.

Mary
Dec 13 '06 #2

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

Dadneo
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
  4.  
  5.     If lngKey <> lngKeepKey Then
  6.         lngKeepKey = lngKey
  7.         lngKeepSeq = 1
  8.     Else
  9.         lngKeepSeq = lngKeepSeq + 1
  10.     End If
  11.  
  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.