sorting ACCESS sql query.

Question

I made another thread which solved my problem, however I got the report reviewed and they want it a revision. I will do my best to explain:

Consider the following:

I just need the ORDER BY part of the sql query. The three fields are ACRNo, Type and Pty.

1. I want it to sort by Type first.

2. I then want it to sort by Pty. However, I want the blank ones at the end. and the sorted ones on the top. (a normal orderby puts the blank ones on top).

3. After that has been sorted, I want the ACR numbers to be Sorted FOR ALL THE BLANK PTY. I dont want the ACR's to be sorted (or i dont really care) when they have a pty attached to them. However when the Pty is blank, i want the highest acrnumber on top.

I hope this makes sense.

Answer 1

ORDER BY
    type,
    IIF(pty IS NULL, 1, 0),
    pty,
    acrno

This assumes that by "blank PTY" you mean NULL. If you want actual empty strings to be at the bottom as well then you'll need to change it slightly:

ORDER BY
    type,
    IIF(NZ(pty, '') = '', 1, 0),
    NZ(pty, ''),
    acrno

SQL sorts on the columns (or expressions) in the order in which they are listed in the ORDER BY clause. So, the above will sort by "type" first, then for rows with the same value for "type" it will sort by the IIF() statement. In this case, the IIF() returns 1 if Pty has no value, otherwise it will return 0. So, the non-valued Pty rows will be sorted after those with a value. Then it goes on to sort by Pty (where all previous expressions in the ORDER BY have the same value) and ACRNo if they have the same Pty value.

Answer 2

There's probably a better way but here's a hackish method that should work unless you have Pty's starting with a bunch of z's:

order by Type, Pty + 'zzz',ACRNo desc