MS Access Order by X unless Y < 0 then order by Z

Question

I've got a bit of an issue in Access whereby I need to order by one column containing time remaining (ORDER BY Round([Days].[DRem],2) but if the number falls below 0 , I need to order by a different column containing text.

The current query is (UPDATED):

SELECT 
    SR.SRNum, 
    Round([qSLADays].[SLDaysRemaining],2) AS SLADRem, 
    SR.SrFreeText
FROM 
    (
        SR 
        INNER JOIN 
        qSLAHours 
            ON SR.RowID = qSLAHours.RowID
    ) 
    INNER JOIN 
    qSLADays 
        ON SR.RowID = qSLADays.RowID
WHERE 
    (
        (
            (Round([qSLADays].[SLDaysRemaining],2))>=0 
                And (Round([qSLADays].[SLDaysRemaining],2))<=1.5
        ) 
        AND 
            ((SR.SRStatus) In ("Open","Resolution Identified")) 
        AND 
            ((SR.SRSubstatus) In ("Assigned","Technical Action","Subject Expert Action","Active Investigation")) 
        AND 
            ((SR.Team)="SWx PAS Support MILL")
    ) 
    OR 
    (
        ((SR.SRSubstatus) In ("Assigned","Technical Action","Subject Expert Action","Active Investigation")) 
            AND ((SR.SrFreeText) Like "PRIORITY*")
    )
ORDER BY Round([qSLADays].[SLDaysRemaining],2) DESC;

I need to Order by SLADRem where SLADRem >0 DESC else order by Freetext ASC but can't see any way to logically do this.

Appreciate any help

I can't add any images of sample output or sample output as I'm unable to add an image. However, anything greater than 0 invariably has no freetext so I firstly need to sort on anything greater than 0. Anything less than 0 needs to be sorted by the freetext field which contains a priority number defined as "PRIORITY0X" E.G.:

 0.93
 0.52
 0.49
 0.16
-6.66 PRIORITY07
-7.34 PRIORITY02
-7.94 PRIORITY09
-8.32 PRIORITY01
-9.15 PRIORITY05

Can you show a little sample data to make it easier to understand what you want to accomplish? And you have way too many brackets there. One of them is even making the query throw a syntax error. — Filipe Silva, Nov 18 '13 at 13:56
you should probably add this to the question and format it the way you want. use {} button to format. — Filipe Silva, Nov 18 '13 at 14:15
you want to order by numeric data, but for some records you want to use alphanumeric data??? I would suggest two queries then. You rresults will normally show all the numeric data either in the first part or the second part of your list, so you might as well just `UNION ALL` your two sets — oerkelens, Nov 18 '13 at 14:21
I've reformatted the query as it reads. and attempted to explain the output with a rough example. Unfortunately, I'm unable to add an image. I tried a union but I'm trying to connect via ODBC and the ODBC doesn't like Union queries — nixxrite, Nov 18 '13 at 14:40

Gord Thompson · Accepted Answer · 2013-11-18T16:02:48.707

One way to handle cases like this is to include a derived column in your query that you can sort on. In your case you could create such a column that included something along the lines of:

one character to control the order of "batches": the positives followed by the negatives,
a nine-digit numeric string to control the sorting for the "positive" batch of records, and
some number of characters from the text field to control the sorting of the "negative" batch

For example, for the sample data [SampleData]

SampleID  SLADrem  Freetext 
--------  -------  ---------
       1     3.14  whatever1
       2     5.00  whatever2
       3     4.20  whatever3
       4     0.00  bravo    
       5    -1.23  alpha    
       6     1.00  whatever4
       7    -2.00  charlie

the query

SELECT 
    IIf(SLADRem>0, "A" & Format(999999 - SLADRem, "000000.00"), "B000000.00") & Left(Freetext, 50) AS SortKey, 
    *
FROM SampleData
ORDER BY 1;

returns

SortKey              SampleID  SLADrem  Freetext 
-------------------  --------  -------  ---------
A999994.00whatever2         2     5.00  whatever2
A999994.80whatever3         3     4.20  whatever3
A999995.86whatever1         1     3.14  whatever1
A999998.00whatever4         6     1.00  whatever4
B000000.00alpha             5    -1.23  alpha    
B000000.00bravo             4     0.00  bravo    
B000000.00charlie           7    -2.00  charlie

that's great, I've nearly got it thanks. I keep getting asked for a value for SLADrem and I have a small issue whereby PRIORITY appears in some positive values and the sort appears to be sorting on the freetext prior to the SLADrem value... any suggestions? — nixxrite, Nov 18 '13 at 15:38
@nixxrite My suggestion would be to take your entire existing query (as posted in your question) *except* the ORDER BY clause and save it as a Select Query in Access under some name. Then use my query as a starting point, substituting [SomeName] for [SampleData]. (You are getting prompted for [SLADRem] because that is a column alias in your query, not a base column.) — Gord Thompson, Nov 18 '13 at 15:48
Thanks Gord, one last question, is it possible to get your query to work down to 2 decimals or is that pushing the boundaries of Access? Thanks again for all the help, really appreciate it. — nixxrite, Nov 18 '13 at 15:56

MS Access Order by X unless Y < 0 then order by Z

1 Answers1