+ Reply to Thread
Results 1 to 4 of 4

Order a query by a list, not ASC or DESC

  1. #1
    Registered User
    Join Date
    11-04-2014
    Location
    Portugal
    MS-Off Ver
    XL2013/XL365
    Posts
    93

    Order a query by a list, not ASC or DESC

    HI

    I'm making a query to an Access database, from Excel using this query "SELECT tblkmsDiarios.Matricula, SUM(tblkmsDiarios.Abastecimento) AS Total, tblMatriculas.UO FROM tblkmsDiarios INNER JOIN tblMatriculas ON tblkmsDiarios.Matricula = tblMatriculas.Matricula WHERE tblkmsDiarios.Matricula<>'OUTRA' GROUP BY tblkmsDiarios.Matricula, tblMatriculas.UO".

    The results are what I want and are correct, what I wanted is for them to be ordered by the 'tblMatriculas.UO' field, but in accordance with the following order ('CON' , 'COGP' , 'COCN' , 'COCS' , ' COGL' , 'COS'), these are the values ​​that 'tblMatriculas.UO' can have.

    Is it possible to do this by changing the query I have?

    Thanks
    Last edited by JCabral; 07-24-2024 at 07:58 AM.

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,778

    Re: Order a query by a list, not ASC or DESC

    Create a table with those values in one column and the sort order (1,2,3 etc) in a second column. Then link that table to the first table and you can order by the sort order column. You may also be able to use something like a CASE WHEN in the ORDER BY clause; I can't recall and it may depend on the provider you are using.

    Edit: I strongly suspect CASE WHEN won't work, so it might have to be nested IIf functions.
    Last edited by romperstomper; 07-24-2024 at 08:11 AM.
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    11-04-2014
    Location
    Portugal
    MS-Off Ver
    XL2013/XL365
    Posts
    93

    Re: Order a query by a list, not ASC or DESC

    Thank you for your answer, I'm not an expert in these matters, I've already tried using CASE WHEN, after searching on Google and it doesn't work

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,778

    Re: Order a query by a list, not ASC or DESC

    Then you'll need nested IIf I think - e.g.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] adding the row number to a desc
    By josvill in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-20-2016, 04:21 AM
  2. [SOLVED] Cumulative SUM of values - in a order specified by a order list 1-10
    By etaf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-30-2015, 03:56 PM
  3. [SOLVED] Auto Populate Order Form, based on qty's filled in Price list - Query
    By magpie10 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-11-2014, 02:08 AM
  4. Replies: 0
    Last Post: 02-15-2014, 10:30 AM
  5. Toolbar tab desc. too lengthy
    By impala096 in forum Excel General
    Replies: 0
    Last Post: 10-05-2010, 12:40 PM
  6. purchase order list & order number generator
    By podaf in forum Excel General
    Replies: 2
    Last Post: 06-28-2009, 06:02 PM
  7. insert a row if DESC and SHIFT change ...
    By vane0326 in forum Excel General
    Replies: 0
    Last Post: 02-06-2005, 11:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1