MS Access Complicated Order By

Question

For an invoicing app I'm working on my PHB has decided that the parts sold listed on the invoice need to go in a very unique order. I would like to accomplish this with a single sql statement if possible.

Essentially the order needs to be as such

1. The most expensive part (but only if there is another part listed at $0)
2. All parts listed at $0
3. All other parts (or all parts) listed by order of part_id
4. All parts with a part_id of ("MISC-30","MISC-31","TEMP")
5. All parts with a negative qty [returns]

There is also a jumble of comments that will need to be added but That will have to be handled by the code

So far I have:

SELECT * 
FROM order_part 
WHERE ordid = 1234 
ORDER BY qty > 0, part_id NOT IN("MISC-30","MISC-31","TEMP"), part_id

However I cannot figure out how to incorporate the first 2 rules

Answer 1

If it's not possible to do this with one select statement, I would write 5 queries that each get the parts of this end query you need with no intersections. Then add a SortBy integer value to each query and union them together (sorting by the SortBy value).

I've done this in SQL Server and I'm guessing this is possible in Access...

Answer 2

Even if this is possible with a single query, I think you owe it to yourself and future developers to make individual queries and join the results together some other way.

The only exception to this is if performance is 100% completely critical and you need to save every microsecond.

But as a developer and manager I'd rather see maintainable code that a junior team member can figure out than some uber-messy SQL statement.

Answer 3

Perhaps you mean you want to have a single recordset i.e. output from your SQL that can be processed by your invoicing App?

Have you thought of the folling -- Its not pretty but it might work.

Select * From
(
    Select 1 as MyOrder .... rest of criteria 1
    Union
    Select 2 as MyOrder .... rest of criteria 2
    Union
    Select 3 as MyOrder .... rest of criteria 3
    Union
    Select 4 as MyOrder .... rest of criteria 4
    Union
    Select 5 as MyOrder .... rest of criteria 5
)
Order by MyOrder

Answer 4

My advice: have the guy that makes you waste your precious time doing such dummy things fired! He must be a slave driver or something? If you can't have him fired, leave the company.

Answer 5

Since you've had to give up being messing long ago on this project ;)

Select * 
    , IIF(((Select Count(*) from order_part 
        where orderid = 1234 and price = 0))=0 
           and price = ((select max(price) from       
               order_part where orderid = 1234
           and qty >0 and part_id not in(("MISC-30","MISC-31","TEMP") 
          )), 1
    , IIf(price = 0, 2
    , IIf(part_id IN("MISC-30","MISC-31","TEMP"), 4
    , IIf(qty < 0, 5
    , 3)))) AS Part_Sort
from order_part
Order By Part Sort, part_id

Really wish Access had case statement. But you can build these nested IIf's and provide a sorting number based on your logic. The final "ELSE" part is the #3 since just sorting by the part ID is the third choice/ doesn't fall under these other categories. Sorry, I know the parenthesis are wrong.