Solved

How to create a consecutive numbers using a SQL sentence

Posted on 2008-10-29
2
679 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2013 - Creating a summarized report 19 35
Expression Evaluater 3 25
T-SQL: New to using transactions 9 25
2 IIF's in Access query 25 26
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 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