none
AutoNumber to Sequence records into MakeTable in MS.Access2010 RRS feed

  • Question

  • I normalize and assemble various records to an access table (about 49,000 records)

    I wish to sort the records as output to another table and sequentially autonumber them to this sorted order

    How can I define an output field to sequentially autonumber in a MakeTable query?

    DatasheetViewing the MakeTable query, the records are correctly sorted in the order I desire

    if I run the MakeTable and view the resultant table they are no longer in the desired sort order

    What is the point of applying Sorts in a MakeTable query if they don't carry to resulting table??

    if I manually apply the sort to the table columns - they are back to the correct sorted order, which I then save

    If I then try to add an autonumber sequence field in DesignView, the records aren't in the order desired after saving


    Jim

    Monday, December 21, 2015 8:15 PM

Answers

  • Try the following:

    • Open the result of the Make-Table query.
    • Delete all records.
    • Switch to design view.
    • Add an AutoNumber field.
    • Close and save the design.
    • Open the query in design view.
    • Change it to an Append query and specify the empty table as target.
    • Run the Append query.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JASelep Monday, December 21, 2015 8:59 PM
    Monday, December 21, 2015 8:22 PM

All replies

  • Try the following:

    • Open the result of the Make-Table query.
    • Delete all records.
    • Switch to design view.
    • Add an AutoNumber field.
    • Close and save the design.
    • Open the query in design view.
    • Change it to an Append query and specify the empty table as target.
    • Run the Append query.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JASelep Monday, December 21, 2015 8:59 PM
    Monday, December 21, 2015 8:22 PM
  • Hi Jim,

    Records in a table are not really stored in any particular order. Sorting is a function of the process when you pull the records from a table. For example, if you're using a query, you can use the ORDER BY clause to sort the records. If you have a primary key, which usually includes an index, then Access will try to pull the records based on that index.

    Another thing is this, an Autonumber field is not guaranteed to produce sequential numbers - only that the value will be unique.

    Not sure if any of this helps with your situation, but just thought I would mention it. If you want to create a table with sequential numbers, I would suggest adding or assigning the sequential numbers to your source data (probably by using a custom function), so that they will show up when you run the make-table query rather than rely on an Autonumber field.

    Just my 2 cents...

    Monday, December 21, 2015 8:50 PM
  • Thanks for this direct bullet-point work-around

    it accomplished my goals without answering any of my questions

    I suppose answering my questions would have far more difficult or impossible from an MS point-of-view then your easy to follow solution

    Thanks again


    Jim

    Monday, December 21, 2015 11:04 PM
  • As the DB guy mentioned, records in a table aren't stored in any specific order. If the table has a primary key, records are displayed ordered by that key by default, but that doesn't mean they're stored that way.

    To compute a sequence number within the query would have been possible, and better in theory, but (a) I don't have enough information to provide details about that, and (b) with almost 50,000 records, calculating the sequence number would likely have been very slow in practice. So I showed you a relatively easy workaround.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 22, 2015 12:07 AM