Force a non-standard sort order

[stepone]

Hello all.

I have a table of 'issues' which can either be 'Resolved', 'Partially resolved' or 'Unresolved'.

I've written a report to summarise the issue by their status, but the report is sorted in alphabetical order, so 'Partially resolved' comes first, followed by 'Resolved'.

What I want to do is to force the records to sort so that 'Resolved' comes first, then 'Partially resolved', then 'Unresolved'. I can get this to happen by adding a calculated field to my query along the lines of "iif(status='Resolved', 1, iif(status='PArtially resolved', 2,3)) and then sorting by the calculated field.

But I have it in the back of my head that there must be a better way to do it.

Is there a better option ?

Thanks very much to anyone that can help out.

Regards,
StepOne

 

[Meltdown]

one way...Add a new table with the three values and pull the values into your combo. Your 'Issues' table shouldn't have repeating data like Resolved, Partially resolved or Unresolved, but the ID from the IssueValues table below.

Table:IssueValues
ID Name
1 Resolved
2 Partially resolved
3 Unresolved

Include the ID value in your query and sort on the ID.

 

[stepone]

Table:IssueValues
ID Name
1 Resolved
2 Partially resolved
3 Unresolved

Include the ID value in your query and sort on the ID.

Thanks Meltdown, that would work. I had it in the back of my mind, though, that there was somewhere you could specify a sort order. I can remember seeing it when using cross-tab queries - you could specify column headings and force them to appear in a particular order. But maybe that's the only place you can do it.

Thanks again,
StepOne