How to create auto numbering field with SELECT statement in Access Jet SQL?

For example I have this query:

Code:

---

SELECT Sort, Field1, Field2 FROM MyTable WHERE Field1='2' AND Field2='1'

---

The field called Sort should be generated and doesn't exist in MyTable. I want "Sort" to be auto numbering field which will start from 0 and then increment it self by 2 every next row, so when I get the results, Sort will contain, 0, 2, 4, 6, etc...

How can I do that inside SELECT statement without creating new table?

Or should I create a helper table which will have Sort as PRIMARY KEY and it will be AUTOINCREMENT?

i am sure you can add a field with autoincrement to your table
if it for once off just do it manually in access
or if regular requirment then use VBA to do

Hmm, maybe a "for" loop with "insert" sql statements.

Quote:

---

then increment it self by 2 every next row

---

afaik autoincrement field can only increase by 1, but i guess, you could add 2 fields, an autoincrement and a a field that doubles it


i found this, which should add field to an existing table, but i have not tested

Code:

---

Dim rt As ADOX.Table, cn As ADOX.Catalog
cn.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\Program Files\Microsoft Office\" & _
      "Office\Samples\Northwind.mdb;"
Set rt = cn.Tables("mytable")
rt.Columns.Append
rt.Fields("ContactId").Properties("AutoIncrement") = True

---

if you only want the extra field to be in a recordset, not affecting the table, you could create a record set on the fly, then insert the table based query into the disconnected recordset, not that i have ever done this

How big are the resultsets going to be?
There is the Option with DCount-Function

Code:

---

SELECT *, DCount("[ID]","[mytable]","[ID]<=" & [ID]) AS row_id
FROM [mytable]
WHERE whatevercriteria

---

Check the design of your table for the PK auto increment field. I think the properties allow you to put the step in. However I am unsure whether you can use 0 as the first number... Post back if you test and whether it works

Quote:

---

Originally Posted by Ecniv

Check the design of your table for the PK auto increment field. I think the properties allow you to put the step in. However I am unsure whether you can use 0 as the first number... Post back if you test and whether it works

---

Not reliable!
He inserts Datasets, ID-AutoIncrement counts up, first fail is selecting from this table using a different order than ID, second fail is, when he deletes datasets, then he has gaps in ID, since autoincrement doesn't care about past values. it still increments upwards

the following should create a table with an autonumber field with seed of 0 and increment of 2
(tested in access 2003

Code:

---

CREATE TABLE  T1 (Sort COUNTER (0,2),Field1 CHAR (20))

---