Results 1 to 4 of 4
  1. #1
    markcrobinson is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    New Hampshire, US
    Posts
    31

    Arrow GROUP BY PROBLEM: Are ORDER BY and FIRST( mutually exclusive?

    My Table: ShortagesDB looks like this:
    TopLevel mFirstOut
    FC1114 5/1/2020
    FC1114 1/1/2021
    FC1114 8/1/2020
    FC1114 5/1/2020
    FC1114 5/1/2020
    FC1114 2/1/2021
    FC1114 6/1/2021
    FC194 6/1/2021
    FC194 2/1/2021
    FC194 6/1/2020
    FC194 8/1/2020
    FC194 5/1/2020
    FC194 1/1/2021
    FC1Corgi68 8/1/2020
    FC1Corgi68 4/1/2021
    FC1Corgi68 5/1/2020
    FC1Corgi68 5/1/2020
    FC1Corgi68 5/1/2020
    FC1Corgi68 5/1/2020

    I want to display only the first occurrence of each top level
    TopLevel mFirstOut
    FC1114 5/1/2020
    FC194 6/1/2021
    FC1Corgi68 8/1/2020

    Created a Query
    TopLevel mFirstOut
    First Group By

    And I get this not sorted properly:
    TopLevel FirstOfmFirstOut
    FC1114 5/1/2020
    FC194 6/1/2020
    FC41212 7/1/2020

    The sql statement is
    SELECT First(Shortagesdb.TopLevel) AS FirstOfTopLevel, Shortagesdb.mFirstOut


    FROM Shortagesdb
    GROUP BY Shortagesdb.mFirstOut

    So I put an Ascending Sort on the TopLevel
    TopLevel mFirstOut
    First Group By

    Ascending

    And I lose my grouping entirely
    SELECT First(Shortagesdb.TopLevel) AS FirstOfTopLevel, Shortagesdb.mFirstOut
    FROM Shortagesdb
    GROUP BY Shortagesdb.mFirstOut
    ORDER BY First(Shortagesdb.TopLevel)

    Are ORDER BY and FIRST( mutually exclusive?
    Do I need to use 2 queries?

  2. #2
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    yes, they are mutually exclusive.


    First() just returns the value in the first record in the recordset returned by the SELECT statement,

    which means that ORDER BY is then irrelevant.

    want to display only the first occurrence of each top level
    I guess it depends on what you mean by "first"

    if you mean first, chronologically, in date order,

    Code:
    SELECT shortagesdb.TopLevel, Min(shortagesdb.mFirstOut) AS mFirstOut
    FROM shortagesdb
    GROUP BY shortagesdb.TopLevel;

    if you want the most recent date then change Min(shortagesdb.mFirstOut) to Max(shortagesdb.mFirstOut)





    good luck with your project,


    Cottonshirt

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    First() and Last() can't always be trusted. Records in a table do not have inherent order - "bolts in a bucket". Maybe you really want Max or Min?

    Also review http://allenbrowne.com/subquery-01.html#TopN

    FC41212 is not in the sample source data. But is in the sample result. What's wrong with the order? Looks ascending to me.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    markcrobinson is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    New Hampshire, US
    Posts
    31
    Perfect! Thank you. I guess I didn't understand "First". The min, giving me the earliest date for each top level was what I needed.

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

Similar Threads

  1. Replies: 2
    Last Post: 02-20-2019, 10:45 PM
  2. Replies: 9
    Last Post: 04-19-2017, 01:36 PM
  3. Radio buttons within Option Group not working
    By losingmymind in forum Programming
    Replies: 7
    Last Post: 01-09-2013, 03:09 PM
  4. Macro stops working for 'USER' group
    By EliOklesh in forum Security
    Replies: 1
    Last Post: 10-28-2011, 07:54 PM
  5. Trying to sum a group of records not working
    By shelbsassy in forum Reports
    Replies: 0
    Last Post: 04-10-2011, 07:52 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