Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to create a consecutive numbers using a SQL sentence

Posted on 2008-10-29
2
680 Views
Last Modified: 2008-11-12
HI

I need to add records to an Access table using "Insert to" sentence, the point is that one of the fields needs to have a progresive value per each record, starting with a number previously defined.

Thanks

0
Comment
Question by:dimensionav
2 Comments
 
LVL 2

Accepted Solution

by:
gm1971 earned 125 total points
ID: 22838396
Hmmm, reaching way back here, but you could create an autonumber column, ramp it with garbage data to the starting number you need and then drop the rows before that.

Or create your own index column starting at the preferred number.
Then before each insert, have a query that pulls back the value of the last record and have it plus one, on the insert....

Don't use my code exactly cuz it sucks, but this might give you the idea....


SELECT MAX(NumIndex) AS MaxNumIndex
FROM myTable

Then in ASP set a variable called NewNumIndex=MaxNumIndex+1

Then on your insert

INSERT INTO myTable
VALUES NewNumIndex

Crappy pseudocode I know, but I'm not an ASP guy and I haven't used Access like that since about 99.
Hopefully this will get you pointed in the right direction though.


0
 

Author Comment

by:dimensionav
ID: 22855950
gm1971:

your code it´s ok (don´t worry), I´ll try it and I´ll let you know.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question