473,326 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to populate sequential numbers in a field using SQL or VBA?

Hi,

Is there a way to populate sequential numbers in a field using SQL or
VBA? If yes, how?

Assume the following is my existing table:

Fruit ID
Apply
Banana
..
..
Orange

and I want to populate the ID field with 100, 101, 102.....

(I do not want to use append query with autonumber field.)

Excel 009

Jun 27 '08 #1
3 4674

"Excel 009" <ex***********@gmail.comwrote in message
news:87**********************************@w1g2000p rd.googlegroups.com...
Hi,

Is there a way to populate sequential numbers in a field using SQL or
VBA? If yes, how?

Assume the following is my existing table:

Fruit ID
Apply
Banana
.
.
Orange

and I want to populate the ID field with 100, 101, 102.....

(I do not want to use append query with autonumber field.)

Excel 009
Create a Temp table with 2 fields
1) AutoNumber
2) Keyvalue

Use a append query to fill the temp table's [KeyValue] with the primary key
value of you data table.
Run an update query using the AutoNumber to fill the ID field
([AutoNumber]-1) + 100.
Jun 27 '08 #2
x = 100

Do While Not rst.EOF

rst.edit
rst![ID] = x
rst.update

x = x +1
rst.movenext
Loop

"Excel 009" <ex***********@gmail.comwrote in message
news:87**********************************@w1g2000p rd.googlegroups.com...
Hi,

Is there a way to populate sequential numbers in a field using SQL or
VBA? If yes, how?

Assume the following is my existing table:

Fruit ID
Apply
Banana
.
.
Orange

and I want to populate the ID field with 100, 101, 102.....

(I do not want to use append query with autonumber field.)

Excel 009

Jun 27 '08 #3
Thanks guys! I will experiment with each of the methods you guys
provide and let you know.

Anyone has a new idea, please feel free to provide. Thanks.

Excel 009
Jun 27 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Jenn L | last post by:
I have a database that is pre-populated with sequential part numbers. As people reserve the parts I update a flag to show the # is no longer available. Now they want the ability to take out a...
5
by: Lapchien | last post by:
I have list of numbers in a table (originally from autonumber in a different database) from 1 to 1,000,000. The list is not in sequential order - there are loads of numbers missing. How can I...
14
by: amywolfie | last post by:
Hi All: I know this is simple, but I just can't seem to get there: I need to sort a table by a text field (txtDescription), then assign sequential numbers to the field SEQUENCE in table. ...
4
by: bill.brennum | last post by:
Hi, This is probably a question with a simple answer, but here goes. I have a form linked to a table. There is a field on that table that while not showing on the form, I would like to...
2
by: Mike Kingscott | last post by:
Hi all, I'd building an app that posts to a web service. One of the things that is required in the soap header is a sequential number appended to a ref, i.e. "IGI1001", "IGI1002", etc. ...
2
by: John | last post by:
Hi I need to assign sequential invoice numbers to orders starting from the last highest number + 1. I have tried the following code; UPDATE Orders SET Orders. = DMax("","Orders")+1 WHERE...
4
by: Bruce | last post by:
Surely someone has done this before, and I am guessing there is a simple solution that is eluding me. I have a simple report based on a recordset. For each record there is a field (RecNum) that...
1
maxamis4
by: maxamis4 | last post by:
Hello folks, I have two forms a parent form and a subform. The parent form is an unbound form while the subform is a form that contains all a list of what I like to call 'in stock ' phone...
9
by: Axxe | last post by:
I have searched high and low for cogent, well-explained coding to complete a project on which I have spent six months of work. I stumbled across something on this site that is close to what I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.