Results 1 to 11 of 11
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Import and sort,

    I figured out how to in VBA: import an excel file, remove/add indexes, add/delete columns/records. What I am looking for is to sort the table by columns.

    First sort by column 1, then sort by column 2, then by column 3, then by column 4 and save the table in that manner, or write to a new table.

    Should I remove all indexes, sort then add index?

    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why should record order matter in table? This is what queries are for.

    So try saving indexed and sorted records to a table that has an autonumber field. What happens?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    June7,
    When I sort the data in such a way it simplifies a count to no end.

    I tried a query where I sort the data by the 4 fields by marking ascending in the appropriate column and it still does not come out the same as when I open the query and right click on the field and select ascending.

    Query 2 is when I open the query and manually sort. Col 1, Col 2, Col 4, Col 5 - This will stay the same after I save it.

    Query 3 is when I select ascending by col 1, Col2, Col 4, Col 5 and run the query.

    I want to programmatically do it without a user going in and sorting. The origin information for my import will not change the way it is output.
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    These datasets do not have all the same fields.

    What output do you want? Query3 essentially sorts by UniqueID, including other fields in sort is irrelevant.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    June7,
    They are the same data sets. 7414 records. Query2 output is what I want, but it has to be done manually.

    Query4 id the output of the table after a new import.
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I imported Query2 into Access and that order was retained. Same for Query4

    I can't replicate Query2 sort without the original dataset that query was built on. I really don't understand how you accomplished it. Sorting always puts Null values first when ascending.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Attached are the 2 files, the CD, and the Excel.
    Attached Files Attached Files

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Explicitly identify field names, which is column1, column2, etc?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Column unique_id first sort ascending, equipment_id, second sort, platform_id third sort, role / FE / I’d last sort

    all ascending. Should end up looking like qeury2

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Do this test. Apply manual sort on Table1 in reverse order of what you listed.

    Now check out this query SQL.

    SELECT Table1.unique_id, Table1.equipment_item_id, Table1.parent_equipment_item_id, Table1.platform_id, Table1.[Role / FE / Node ID]
    FROM Table1
    ORDER BY Table1.[Role / FE / Node ID], Table1.platform_id, Table1.equipment_item_id, Table1.unique_id;
    Last edited by June7; 01-24-2019 at 04:06 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    June, That works, thanks. I just found out
    DoCmd.RunSQL doesn't works for SELECT query. Now I have to change the import way.
    Import as temp, delete the stuff out, run the query to make table, add fields, delete temp table, go from there.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-09-2017, 11:14 PM
  2. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  3. Replies: 1
    Last Post: 04-07-2014, 02:59 AM
  4. sort filter, doesn't sort everything
    By deso in forum Queries
    Replies: 4
    Last Post: 01-25-2013, 05:27 AM
  5. How to sort by three sort orders
    By captgnvr in forum Access
    Replies: 4
    Last Post: 11-09-2009, 07:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums