Results 1 to 3 of 3
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143

    Sort issue- how to resolve

    ct_crosstab1.zipOn May 9th I posted a query to arrange the location. June7 solved it , and it worked fine until now. But I have values A8, A9, A10, A12, A13 in my tables which is causing sorting issues because when there is 8,9,10. it's taking 10 as the min value and 9 as max value. and not showing 8 at all.

    I google to find a solution and some said to have the fields as Integer but that did not work.
    any other suggestions to resolve the sort issue?

    Thank you in advance.

    TRANSFORM Max(Wells.Cnum) AS MaxOfCnum
    SELECT Left([Well],1) AS WellGrp, Min(Mid([well],2)) AS WellMin, Max(Mid([well],2)) AS WellMax, Wells.well_sort
    FROM Wells


    GROUP BY Left([Well],1), Wells.well_sort
    PIVOT Wells.rank;

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, I should have recognized that issue in the original suggestion.

    TRANSFORM Max(Wells.Cnum) AS MaxOfCnum
    SELECT Left([Well],1) AS WellGrp, Min(CInt(Mid([well],2))) AS WellMin, Max(CInt(Mid([well],2))) AS WellMax, Wells.well_sort
    FROM Wells
    GROUP BY Left([Well],1), Wells.well_sort
    PIVOT Wells.rank;
    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
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Thank you so much, CInt function worked. In SAS programming there are different options, we can use order= internal or order=formatted etc.

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

Similar Threads

  1. Replies: 4
    Last Post: 05-28-2019, 04:54 PM
  2. Sort order issue with Combo box
    By elcunliffe in forum Database Design
    Replies: 12
    Last Post: 04-06-2017, 07:54 AM
  3. Replies: 7
    Last Post: 02-06-2017, 03:02 AM
  4. Replies: 1
    Last Post: 09-22-2016, 10:26 AM
  5. Replies: 2
    Last Post: 10-18-2012, 06:56 PM

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