Why do I lose the sort order?

[RobJDB]

Hi.
I'm importing a tab-delim'd txt file to Access. I then need to sort it by one of its fields. Then, I need to add an autonumber PK, but when I do this, the order reverts to how it was on import. Is it not possible to re-order and then maintain this new order when I add my primary key?

Many thanks,

Robert

 

[hjgoldstein]

Robert,

I have seen this problem also. Don't know why it happens, but there is a work-around by sorting in Excel first and then importing (either the .xls or saving as .txt as before,) then the import will be in the order required.

Of course, if you need all of the process automated from original .txt to the Access table sorted and indexed, it'll be more complicated. I'm afraid my expertise doesn't go that far (yet???!!!)

HTH

 

[RobJDB]

Thanks HTH,

In the end I wrote a little module to go through the table in the required order and set the value of my new field using a simple incremental.

It seems to me that when you sort a table, Access doesn't really re-order the records, it just *displays* them in the new order. I'd be surprised if there's not a way to actually 'apply' the sort to the table.

Anyone know....?

Robert

 

[GDGarth]

Instead of importing, create a linked table to your data.

Then, create a make-table query(or append query, if the destination table already exists), sorting however you want. This will take the data from your linked table and put it in the new table in the correct order.

If you do this routinely, you can automate it easily.

 

[cmmrfrds]

To my knowledge there is no implied order in an sql table. The data may appear in order if the table is relatively static (few inserts, updates, deletes), but this is not reliable. That is what the order by clause is for. Is there some reason the "order by" will not take care of your requirements?