By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Sequence Query in Access

P: 52
Hi...I had a question regarding sequence utilization in query. I have an access database where i add a new field which is AltId where i want the field to filled with numeric values that start from 1. Its some sort like auto increment but i wan it run by query.how can i do so?
I plan do do with sequence syntax...but i dont know how to use it efficiently
i do like below.

Create SEQUENCE sequencename1 START WITH 1 INCREMENT BY 1 then
ALTER TABLE Alternator ALTER AltID RESTART WITH 1

but i got error....How plsssssssssss help me......Urgent...Thanks in advance
Apr 28 '08 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
I plan do do with sequence syntax...but i dont know how to use it efficiently
i do like below.

Create SEQUENCE sequencename1 START WITH 1 INCREMENT BY 1 then
ALTER TABLE Alternator ALTER AltID RESTART WITH 1
Hi, there.

JetSQL unlike PLSQL has no SEQUENCE facility. It uses Autonumber type field instead

I have an access database where i add a new field which is AltId where i want the field to filled with numeric values that start from 1. Its some sort like auto increment but i wan it run by query.how can i do so?
What do you mean? Do you mean to store the numbers in table? Do you mean query returning enumerated records?

Kind regards,
Fish
May 1 '08 #2

P: 52
i want to insert values into not an auto increment fields . I want the field fill the values start from 1 till the n last record. i wan to run a query to do so. plzzz help me by query.
May 2 '08 #3

FishVal
Expert 2.5K+
P: 2,653
i want to insert values into not an auto increment fields . I want the field fill the values start from 1 till the n last record. i wan to run a query to do so. plzzz help me by query.
I would recommend you to write a very simple VBA function with a static variable incrementing the variable value each time the function is called, then use it in update query to generate number sequence.

Expand|Select|Wrap|Line Numbers
  1. 'varDummy is used to enforce Access to run it for each record in query
  2. 'if varDummy is missed then enumerator reset
  3. Public Function NumSeq(Optional varDummy As Variant) As Long
  4.  
  5.     Static lngNum As Long ' static var preservs value between calls
  6.  
  7.  
  8.     If IsMissing(varDummy) Then
  9.         lngNum = 0
  10.     Else
  11.         lngNum = lngNum + 1
  12.     End If
  13.     NumSeq = lngNum
  14.  
  15. End Function
  16.  
  17. Public Sub ApplySeqToTable()
  18.  
  19.     'reset enumerator
  20.     NumSeq
  21.     'run update query
  22.     DoCmd.RunSQL "UPDATE t1 SET t1.lng = NumSeq(t1.txt);"
  23.  
  24. End Sub
  25.  
Regards,
Fish
May 2 '08 #4

P: 52
"This operation requires an open database " error appear
May 3 '08 #5

FishVal
Expert 2.5K+
P: 2,653
"This operation requires an open database " error appear
Ok.
SQL statement
Expand|Select|Wrap|Line Numbers
  1. UPDATE t1 SET t1.lng = NumSeq(t1.txt);
  2.  
I've posted above has the following definitions
  • t1 - name of the table
  • lng - name of the field number sequence is stored to
  • txt - name of any (no matter which) field in the table
You need to replace it with that relevant in your database.

Regards,
Fish

BTW, if you add Autonumber field it will be filled with number sequence automatically.
May 3 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.