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

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