-
Jun 29th, 2018, 08:47 AM
#1
Thread Starter
Addicted Member
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?
-
Jun 30th, 2018, 01:42 AM
#2
Re: How to create auto numbering field with SELECT statement in Access Jet SQL?
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 30th, 2018, 11:23 AM
#3
Thread Starter
Addicted Member
Re: How to create auto numbering field with SELECT statement in Access Jet SQL?
Hmm, maybe a "for" loop with "insert" sql statements.
-
Jun 30th, 2018, 05:17 PM
#4
Re: How to create auto numbering field with SELECT statement in Access Jet SQL?
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jul 2nd, 2018, 03:13 AM
#5
Re: How to create auto numbering field with SELECT statement in Access Jet SQL?
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jul 3rd, 2018, 02:49 PM
#6
Re: How to create auto numbering field with SELECT statement in Access Jet SQL?
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
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Jul 4th, 2018, 01:05 AM
#7
Re: How to create auto numbering field with SELECT statement in Access Jet SQL?
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jul 6th, 2018, 04:29 AM
#8
Re: How to create auto numbering field with SELECT statement in Access Jet SQL?
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))
do not put off till tomorrow what you can put off forever
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|