Sequence Query in Access

[vaniKanabathy]

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

[FishVal]

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

[vaniKanabathy]

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.

[FishVal]

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.

[code=vb]
'varDummy is used to enforce Access to run it for each record in query
'if varDummy is missed then enumerator reset
Public Function NumSeq(Optional varDummy As Variant) As Long

Static lngNum As Long ' static var preservs value between calls


If IsMissing(varDu mmy) Then
lngNum = 0
Else
lngNum = lngNum + 1
End If
NumSeq = lngNum

End Function

Public Sub ApplySeqToTable ()

'reset enumerator
NumSeq
'run update query
DoCmd.RunSQL "UPDATE t1 SET t1.lng = NumSeq(t1.txt); "

End Sub
[/code]

Regards,
Fish

[vaniKanabathy]

"This operation requires an open database " error appear

[FishVal]

"This operation requires an open database " error appear

Ok.
SQL statement
[code=sql]
UPDATE t1 SET t1.lng = NumSeq(t1.txt);
[/code]
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.