Results 1 to 7 of 7
  1. #1
    bilalo is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    107

    Query with custom sort

    I have a table named customer with P.K CName that contains the names of customers. One of the customers is called "Cash".
    I want to get a list of all CName with "Cash" to be the first retrieved item.

    I used the following query to put "Cash" at the top of the list but the results are still sorted and "Cash" is still in the middle of the list
    SELECT 'Cash'
    FROM Customer
    union
    SELECT CName
    FROM Customer


    WHERE CName<>'Cash'

    I think the sorting was applied after the union. Is there a way to perform this kind of custom sort?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    SELECT 'Cash', 1 As SortField
    FROM Customer
    union
    SELECT CName, 2 As SortField
    FROM Customer
    WHERE CName<>'Cash'

    Which gives you a field to sort on.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    No need for a Union Query.
    Just create a calculation to sort on, i.e.
    Code:
    IIF(CName='Cash',1,2)
    So that code would look like:
    Code:
    SELECT CName
    FROM Customer
    ORDER BY IIF(CName='Cash',1,2), CName;

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Good point. Sometimes I'm overly focused on "fixing" what was posted.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Good point. Sometimes I'm overly focused on "fixing" what was posted.
    I cannot tell you how many times I have done that myself (usually, I am on the other end)!

  6. #6
    bilalo is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    107
    Thank you guys, nice ideas.

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!

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

Similar Threads

  1. Replies: 2
    Last Post: 05-27-2014, 11:32 AM
  2. Combo Box - Custom Sort Order
    By twildt in forum Access
    Replies: 4
    Last Post: 05-22-2014, 01:38 PM
  3. Replies: 1
    Last Post: 04-07-2014, 02:59 AM
  4. Custom Query Help
    By samanthaM in forum Access
    Replies: 8
    Last Post: 11-04-2012, 08:35 PM
  5. Replies: 1
    Last Post: 02-27-2012, 06:22 PM

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