Sorting

[peljo via AccessMonster.com]

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 ?

 

[Guest]

The SQL statement needs an order by clause, i.e.

strProducts = " SELECT products.Productid, products.branch0 ,
products.items0 INTO ProductsTemp FROM products ORDER BY products.Productid"

 

[TC]

In a relational database like MS Access, >>> tables do not have a
natural order <<<. You need to get that assumption completely out of
your head.

If you want your SQL statement to return the records in order, you have
to include an ORDER BY clause. For example:

strProducts = " SELECT products.Productid, products.branch0 ,
products.items0" & _
" INTO ProductsTemp" & _
" FROM products" & _
" ORDER BY products.Productid"
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

HTH,
TC (MVP Access)
http://tc2.atspace.com