[RESOLVED] Access Moving Records

[Lasering]

Hi!!

I've created a table in access with a primary key and 10 records. If i want to change the order of the records for example the record number 8 i need to be record number 1, i change the record number 8 id to 11 (so i wont get and error saying that id already exists), then the record number 1 id to 8 and finnaly the record number 8 id to 1. But if in my table i dont have a id columm and NO primary key how can i change the order of the records???

By the way the Database format is Access 2000.

[GaryMazzone]

The norm in database is to use an index to garentee the order of results in a database. Is there some way you can place in index on the column you want and get it in the order you want? Or add a new column named OrderBy (set as an Number --> Long Integer) and place the order 1 to what ever there then Set an index on that new column.

[si_the_geek]

There is a very important conceptual point you don't seem to understand... records in a database do not have an order, they are just a set of records that can be given out in any order that the database system sees fit - unless you specify otherwise.

Generally Access will give you records in the order they were entered, but there are situations where it wont.


If you care what order the fields are returned in, the only safe thing to do is to specify it - typically this will be done by appending an Order By clause to an SQL statement (such as: .. FROM tablename ORDER BY field1 ).

If you want records to be returned in a certain order each time, a separate field to specify the order (edit: as Gary mentioned) is a good method.

[Lasering]

How do i set an index on the new columm?

And this columm that i add can i delete it after? Because the table can only has the columm it has now otherwise my program gives an error

[GaryMazzone]

Here is an image shoing the parts. No the column would need to remain to leave the index in place. What to you mean the app fails? What code are you running?

[Lasering]

I did like this. I went to Structure View and added a field named index as a number. Then went to datasheet and in the columm index inserted the numbers in the order i want the records. Then in structure mode in Indexes puted index, index, ascending for Index name, Field Name and Sort Order.
This maked my records in the order i want but with i another field that i DONT want.

Isnt there any way to move lines like in Excel??If not why not?

[si_the_geek]

Is it really so bad having an extra field?

Just because the field is there, it doesn't mean that you have to show it in your application.

[Lasering]

i now that, but in all my other tables i dont need it. And my program is designed to add all colums from the tables of the DB, if it gets one more field to add it will give an error because isnt coded to deal with that field. In access he sould be able to move colums as in excel a lot easier and a lot less work.

[GaryMazzone]

The field must stay if you want the order like that. Just change the code that copies the table and directy list the fields to select instead of a *

[salvelinus]

Access is not Excel. As Si pointed out, records in a database - any relational db, anyway, Access, Oracle, SQL Server, MySql, etc - aren't guaranteed to be in any particular order. Data in a table doesn't reside in a particular cell, as it does in Excel, even though datasheet view looks a lot like a spreadsheet.
I believe this is true even with indexed fields, if you mean you look at the table in datasheet view, or you bind (bad idea) to a table and want them ordered. You need the WHERE clause in SQL to do this.
You can use SQL ALTER TABLE to add or drop fields, but that would be a lot of extra work - add field, populate it somehow with the order you want, order by that field (Sort is the Excel equivalent), do whatever you want to actually do with the data, then drop the added sort field. And you'd have to do that every time you needed this.
If having an extra field is a problem, I'd suggest modifying whatever code adds all the columns to just add the ones you want, rather than all of them.
If your code is INSERT INTO tbl1 SELECT * FROM tbl2, for example, try INSERT INTO tbl1 (fld1, fld2...) SELECT fldA, fldB... FROM tbl2.
If this is an import specification, say to get data from Excel, either modify the specification or create a new one.