?
Solved

How to create a consecutive numbers using a SQL sentence

Posted on 2008-10-29
2
Medium Priority
?
691 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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

765 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