Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ascending order (Access 2000)

    When i make a new table, sometimes,but not always, the products are not sorted in an ascending order, i do now know why but it happens.My commmand with which i make the table is the folowing
    Private Sub Command6_Click()
    Dim strProducts As String
    strProducts = " SELECT products.Productid, products.branch0 , products.items0 INTO ProductsTemp FROM products"
    CurrentDb.Execute strProducts
    End Sub
    I am applying an attachement.In this atachement the command has made a table where the last product is number 467. There are however also products number 471,472 and 473 but they are placed somehow higher u in the table, not below 467.What could i add on in my command so that to avoid such cases and the products be arranged in their ascending order ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ascending order (Access 2000)

    Records in an Access table do not have an intrinsic sort order, and it isn't really important anyway - you can sort them the way you want in a query. For some purposes, you may want to sort them on ProductID, for others on Branch0 or on Items0.

    If you want a default sort order in the table, you must set a primary key. A make-table query (SELECT ... INTO ...) does not create a primary key, so it would be better to create a table ProductsTemp with the correct structure, including a primary key on ProductID, and append the records to this table:

    ' Delete all existing records
    strProducts = "DELETE * FROM ProductsTemp"
    CurrentDb.Execute strProducts
    ' Append new records
    strProducts = "INSERT INTO ProductsTemp (ProductID, Branch0, Items0) SELECT ProductID, Branch0, Items0 FROM Products"
    CurrentDb.Execute strProducts

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ascending order (Access 2000)

    Thank you. It is clear to me now and you helped me a lot

    Peljo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •