Results 1 to 8 of 8

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

  1. #1

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

    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?

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

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

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

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  6. #6
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    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

    BOFH Now, BOFH Past, Information on duplicates

    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...

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

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

    Quote Originally Posted by Ecniv View Post
    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

  8. #8
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    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
  •  



Click Here to Expand Forum to Full Width