Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sort order gets lost

    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.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order gets lost

    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.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sort order gets lost

    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.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order gets lost

    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??

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order gets lost

    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.

  6. #6
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order gets lost

    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.

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order gets lost

    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.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order gets lost

    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.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order gets lost

    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.
    Legare Coleman

  10. #10
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order gets lost

    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.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sort order gets lost

    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.
    Charlotte

Posting Permissions

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