Solved

How to create a consecutive numbers using a SQL sentence

Posted on 2008-10-29
2
685 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
[X]
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
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

728 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