sort order gets lost

Topic

New Reply

I have a table of data that was input in random order. I want to sort it on one of the fields, then autonumber the resulting table. When I sort the table, then add the autonumber field, the sort order reverts to its original order.

What’s happening? How can I make Access “forget” the original order??

Don.

Reply | Quote

Replies

Make a copy of the structure with no records. Next make an append query and set the sort order in the order you want the records to append to the copy. Once the records are in the copy in the order you want them to be, create the autonumber field.

Reply | Quote

Did you make the autonumber the primary key? It isn’t the values themselves, it’s the indexes that determine how a table displays. By default tables display in primary key order. However, it is not a good idea to depend on the order of the records displayed in the table, since that is irrelevant in relational databases.

Reply | Quote

Sorry, neither Charlotte’s or Judy’s proposed solutions works. As soon as I click on “insert field”, the original sort order returns, even if I sort the table first. And Access won’t let me add the field, sort, and then change the field type to autonumber.

The strange thing is that this table was sent to me as a comma-delimited ASCII file created by another database program, and I imported the comma-delimited file into Access. So any original indexing information should not have transferred over when the file was converted to an ASCII file. Or is this a wrong assumption??

Reply | Quote

I would have to agree with Charlotte and ask why are you putting so much emphasis on sorting the table?

Having said that try creating the autonumber field in the new table before appending the sorted records to the new table.

Reply | Quote

The reason I need the table in sorted order is that it is a directory to be published with Quark, and I have to export it in comma-delimited format to a Mac-based graphics department. So the records must be in the final order that they will appear in the directory.

Paul’s solution worked! Thanks to all for the suggestions.

Don.

Reply | Quote

Glad it worked for you. For future reference you can export a query as well as a table which should give you the ability to sort the table data how you want.

Reply | Quote

But if you add additional records, or correct an error in the field they were sorted on, then they are no longer going to be in the correct order. What you should be doing is to create a query that contains the fields that you want to export to Quark, and that sorts the records into the desired order. Then export the query to Quark. That export should always be correct, and you won’t have to go through all of these manual manipulations every time you need to export the data.

Reply | Quote

The problem is that Quark won’t be able to do anything with the query because Quark is a Mac platform, and there is no Mac version of Access.

Reply | Quote

No, you don’t export the query, you export the records in the query to a text (or some other external) format. When anyone refers to “exporting” a query, that’s usually what they mean. The exception is when you export the query to another Access database. Then you’re actually exporting the SQL that generates the recordset.

Reply | Quote

Create a query using your initial table. Select all the fields to appear in the grid at the bottom of the query design screen except for any autonumber field you may currently have. Set the sort for the field you want to ascending in the sort row. Select Query, Make-Table Query from the menu bar and type a name for the new sorted table. Run your query and your new table will be in the order you want. In the new table go into design view and create a new field and set the data type to autonumber. Your records should now be in the order you want and numbered consecutively.

Reply | Quote