Sorting non alphabetically

[Guest]

I have a field that displays a records RAG Status (Red, Amber, Green). I'd
like to be able to sort the records in this order (i.e. non-alphabetically)
but cannot work out how to do this. Can anyone help?

 

[Jamie Collins]

I have a field that displays a records RAG Status (Red, Amber, Green). I'd
like to be able to sort the records in this order (i.e. non-alphabetically)
but cannot work out how to do this. Can anyone help?

SELECT RAG_status
FROM Test
ORDER BY INSTR('Red**AmberGreen', RAG_status);

Jamie.

--

 

[Guest]

You ideally should have a lookup table containing these values and a column
that identifies the sort order. You could then add this table to the report's
record source which would make the sort field available.

Otherwise, you can use an expression in the sorting and grouping like:
=Instr("Red, Amber, Green",[RAG Status])

 

[Marshall Barton]

I have a field that displays a records RAG Status (Red, Amber, Green). I'd
like to be able to sort the records in this order (i.e. non-alphabetically)
but cannot work out how to do this.

If it just those 3 values, you might be able to get away
with sorting on the expression:

Switch([RAG Status] = "Red",1, [RAG Status] = "Amberr",2,
[RAG Status] = "Green",3, True,0)

But a better way would be to include a status code field
with the sorting order values in a lookup table.

 

[Jamie Collins]

SELECT RAG_status
FROM Test
ORDER BY INSTR('Red**AmberGreen', RAG_status);

It's good practice to expose the sort order in the resultset:

SELECT RAG_status, INSTR('Red**AmberGreen', RAG_status) \ 5 AS
sort_order
FROM Test
ORDER BY 2;

Jamie.

--