Results 1 to 10 of 10
  1. #1
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79

    Adding Exceptions to SQL's ORDER BY Statement


    I am trying to assemble a query and I'm having trouble with sorting. I am correctly querying the records that I desire. However, I want a very particular ordering. I want to ORDER BY a field, in this case name. But I wish for a one particular name to appear before all the rest, and then the rest would sort alphabetically . I found a similar problem on stackoverflow (https://stackoverflow.com/questions/...ith-exceptions) I tried implementing the "CASE WHEN" statement, but Access doesn't seem to like that.

    Thank you in advance.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Access SQL does not use CASE.

    What is special about the name to be first in name order?? A little more info please.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I suggest a UNION query. First part for the one particular name; second part for the rest.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Add a field to your query, with an expression as its data source. The expression could be something like

    Special: IIf([Lastname] = "specialname", 1, 0), where [Lastname] is the lastname field in the query, and specialname is the value that is the priority.

    Sort by this field first, then the other fields as required. If you are using the query design grid, you will have to put this field first (left) so that the sort is correct, but it does not have to be visible.

  5. #5
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79
    Quote Originally Posted by orange View Post
    Access SQL does not use CASE.

    What is special about the name to be first in name order?? A little more info please.
    The name I want to appear first has the word General in it. Therefore in order to isolate it from any other records I usually use the phrase " LIKE '*General' ". Is there some other alternative than the "CASE WHEN" statement?

  6. #6
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79
    Quote Originally Posted by John_G View Post
    Add a field to your query, with an expression as its data source. The expression could be something like

    Special: IIf([Lastname] = "specialname", 1, 0), where [Lastname] is the lastname field in the query, and specialname is the value that is the priority.

    Sort by this field first, then the other fields as required. If you are using the query design grid, you will have to put this field first (left) so that the sort is correct, but it does not have to be visible.
    I like this, but I am a little confused. I understand that we are attributing a binary value to something based on the comparison to "specialname". However, do we have to declare anything else like " AS Ordering " or something along the lines of the to reference this value in the " ORDER BY " clause?

  7. #7
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79
    Code:
     SELECT *, 
    CASE WHEN Program LIKE '*General' THEN 0 ELSE 1 END AS Ordering 
    FROM [tblStatus] 
    WHERE OGAuthor IN (SELECT FullName FROM tblUsers WHERE tblUsers.Group = 'Embedded Systems (Commercial & Navy Programs)') AND ((StartDate BETWEEN #8/22/2017# AND #8/23/2017#) OR (Ongoing = True)) 
    AND OGAuthor IN (SELECT FullName FROM tblUsers WHERE tblUsers.Department = 'SW')
     AND Urgent = True 
    AND isCurrent = True 
    ORDER BY Ordering, Program, SubProgram, Task, Initials DESC, StartDate DESC, OGStartDate DESC, ID ASC;

    This was my attempt at recreating what was on StackOverflow. As @orange pointed out, this doesn't work because Access SQL does not support the CASE WHEN clause. However, is there a way to implement the " Iff() " command using the " SELECT * " phrase? Or will I have to type out each field I want individually? I was a little confused on the meaning of the comma after the SELECT * clause and the AS statement. If anyone has any insight on those I'd greatly appreciate it!

  8. #8
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79
    Code:
    SELECT IIf([Program] Like '*General', 1, 0) AS Ordering, Program, Task, ID, OGID, StartDate, OGStartDate, Author, OGAuthor, Initials, BodyText, OldStatus, Ongoing, Urgent, nEdits 
     FROM [tblStatus] 
      WHERE OGAuthor IN 
       (
        SELECT FullName 
         FROM tblUsers 
          WHERE tblUsers.Group = 'Embedded Systems (Commercial & Navy Programs)'
       ) 
      AND 
       (
        (StartDate BETWEEN #8/22/2017# AND #8/23/2017#) OR (Ongoing = True)
       ) 
      AND OGAuthor IN  
       (
        SELECT FullName 
         FROM tblUsers 
          WHERE tblUsers.Department = 'SW'
       ) 
      AND Urgent = True 
      AND isCurrent = True 
     ORDER BY Ordering, Program, SubProgram, Task, Initials DESC, StartDate DESC, OGStartDate DESC, ID ASC;
    So I've gotten rid of the CASE WHEN clause and the SELECT *. I've changed the SELECT statement to define 'Ordering' based on the [Program] being like "*General". I'm getting an error upon CurrentDb.OpenRecordset. ErrorNumber = 3061, Too few parameters. Expected 1.

    Not sure where the issue is..

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    is there a way to implement the " Iff() " command using the " SELECT * " phrase?
    Actually, that SQL does the exact thing I was suggesting. You just have to change the syntax to what MS Access will accept.

    The CASE statement,
    CASE WHEN Program LIKE '*General' THEN 0 ELSE 1 END as Ordering (which you can't use)

    is equivalent to

    IIf([Program] LIKE "*General", 1, 0) as Ordering

    Just like the CASE, the IIf returns a 1 if the value in "Program" ends in "General", and 0 otherwise.

    Now if you look at the ORDER BY, you see that the sorting is done on Ordering first, followed by the other fields.

    So, you are actually almost there. I can't be sure, but you might not be able to use the "Ordering" in the order by - you might have to put the IIf in there instead.

    I'm getting an error upon CurrentDb.OpenRecordset. ErrorNumber = 3061, Too few parameters. Expected 1.
    Try changing "Ordering" in the ORDER BY to the IIf just as you have it in the Select.


    Last edited by John_G; 08-23-2017 at 12:59 PM. Reason: Add more info

  10. #10
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79
    Code:
    SELECT Program, SubProgram, Task, ID, OGID, StartDate, OGStartDate, Author, OGAuthor, Initials, BodyText, OldStatus, Ongoing, Urgent, nEdits 
     FROM [tblStatus] 
      WHERE OGAuthor IN 
       (
        SELECT FullName 
         FROM tblUsers 
          WHERE tblUsers.Group = 'Embedded Systems (Commercial & Navy Programs)'
       ) 
      AND 
       (
        (StartDate BETWEEN #8/22/2017# AND #8/23/2017#) OR (Ongoing = True)
       ) 
      AND OGAuthor IN 
       (
        SELECT FullName 
         FROM tblUsers 
          WHERE tblUsers.Department = 'SW'
       ) 
      AND Urgent = True 
      AND isCurrent = True 
     ORDER BY IIf([Program] Like '*General', 1, 0) DESC, Program, SubProgram, Task, Initials DESC, StartDate DESC, OGStartDate DESC, ID ASC
    ;
    It's working! You were totally right. ORDER BY could not use 'Ordering' as a sorting parameter.. Instead introducing the condition directly into the ORDER BY clause worked perfectly, all without defining an dummy 'Ordering' field.
    Thank you guys so much!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Allow edits with exceptions
    By boboivan in forum Access
    Replies: 11
    Last Post: 05-30-2016, 07:27 AM
  2. Adding Parts to a Repair Order
    By joecamel9166 in forum Access
    Replies: 2
    Last Post: 04-07-2016, 12:07 PM
  3. SQL Query Statement - ORDER BY problem
    By hinsdale1 in forum Forms
    Replies: 5
    Last Post: 04-24-2013, 08:40 AM
  4. Replies: 4
    Last Post: 11-02-2012, 11:00 PM
  5. delete query with exceptions
    By Jerseynjphillypa in forum Queries
    Replies: 3
    Last Post: 07-11-2012, 08:07 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums