MS Access SQL Sorting with conditions?

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?

Answer 1

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

---

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

Answer 2

Here is the fully qualified list:

Add
Changeprop
Insert
Move
Remove

Thanks!

Answer 3

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)

Answer 4

This query will do that:

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

---

Gustav Brock

Answer 5

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.

Answer 6

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)

Answer 7

Hi Hans -

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