Sorting non alphabetically

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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?
Click to expand...

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

Jamie.

--
 
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])
 
TomTomRBS said:
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.
Click to expand...

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.
 
SELECT RAG_status
FROM Test
ORDER BY INSTR('Red**AmberGreen', RAG_status);
Click to expand...

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.

--
 
Back
Top