none
MS Access SQL Sorting with conditions? RRS feed

  • Question

  • Hi Folks -

    I have the following query that I need to update:

    SELECT *
    FROM tblActionScript
    ORDER BY tblActionScript.[Action], [Param3];
    

    I need to sort [Action] like it is (A,B,C etc) however, I need anything that starts with "R" to come before anything that starts with "I".

    For instance, this is how it sorts:

    Action
    Add
    ChangeRequest
    Insert
    Remove
    

    But I need to sort like this:

    Action
    Add
    ChangeRequest
    Remove
    Insert
    

    I can't seem to get it. Is it possible?

    Wednesday, May 27, 2020 10:58 AM

All replies

  • What values can the Action field have? Is it a fixed list of possible values?

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


    Wednesday, May 27, 2020 11:13 AM
  • Here is the fully qualified list:

    Add
    Changeprop
    Insert
    Move
    Remove

    Thanks!

    Wednesday, May 27, 2020 11:18 AM
  • I'd create a table tblAction like this:

    and change the query to

    SELECT tblActionScript.*
    FROM tblActionScript INNER JOIN tblAction ON tblActionScript.Action = tblAction.Action
    ORDER BY tblAction.SortOrder, tblActionScript.Param3;


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

    Wednesday, May 27, 2020 11:57 AM
  • This query will do that:

    SELECT 
        Action
    FROM 
        tblActionScript
    ORDER BY 
        Abs([Action] < "I") DESC, 
        Abs([Action] Like "R*") DESC, 
        Action;


    Gustav Brock

    Thursday, May 28, 2020 9:53 AM
  • Hi Hans -

    Sorry to bring this thread back from the dead, but just wanted to let you know it's working great!!  I am running into one limitation.  I have certain "nodes" that need to come "first" in the results, in this order:

    PFC

    PFR

    PFG

    PFD

    PFI

    PFP

    IND

    These are found in tblActionScript.Param3.  So I need to filter Param3 as well based on these values in the above order. Because right now Param3 data elements with "PFP" are coming before "PFC" which is incorrect for my use case.   Is that possible to layer this additional sort in? I've been trying with no luck.


    • Edited by cdtakacs1 Saturday, July 18, 2020 9:44 AM
    Saturday, July 18, 2020 9:43 AM
  • Create another table tblParam3:

    And change the query to

    SELECT tblActionScript.*
    FROM (tblActionScript INNER JOIN tblAction ON tblActionScript.Action = tblAction.Action) INNER JOIN tblParam3 ON tblActionScript.Param3 = tblParam3.Param3
    ORDER BY tblAction.SortOrder, tblParam3.SortOrder


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

    Saturday, July 18, 2020 10:32 AM
  • Hi Hans -

    This worked perfectly!!!!! Thank you so much!!!

    Sunday, July 19, 2020 9:09 AM