none
Sorting order and First() function RRS feed

  • Question

  • I am looking to have a table be able to be sorted by a certain criteria and then after that sort have the first record be pulled from each category.  I thought maybe sorting the table and then using a first () would give me this but it seems it ignores the sorting and just gives me the first record in order of how they were in the original table.

    Here is what I am trying to do...i have a table that is a list of check off items for different projects and I would like the query to return to me the next task item that needs to be done in the list (next non checked off item based on priority level, priority level is set by a field and is not necessarily in the order that they are in the table, hence the need for sorting).  Ultimately i would like to let the user know what the next task is for each active project.

    Any suggestions on how to get this info?

    Monday, November 27, 2017 9:40 PM

All replies

  • First() and Last() don't do what a lot of people think they do. They pull the "first" or "last" record based on how the table is indexed. Use Min() to get the first in your sorted table, but you have to base it on the field you are sorting on.



    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, November 27, 2017 9:52 PM
  • I'll try giving that shot and see if it will do the trick.

    My table has the following fields:

    MstrUID - this is what I am looking to group by in the end its my project iD

    MstrListUID - this is the item I am trying to reference as the next list item

    complete - this is a simple check box if the task has been completed or not

    CatPriority - this is the priority level of the category, basically the first sorting level

    Priority - this is the priority of the task within the category, the second sorting level

    With these I am trying to have it sort by CatPriority then Priority (lowest number of each) and complete must be false with those criteria give me the first record in MstrListUID for each MstrUID group


    • Edited by spezoinks Monday, November 27, 2017 10:09 PM
    Monday, November 27, 2017 10:09 PM
  • It looks like Priority is the field you should use with the Min() aggregate for each of your CatPriority groups.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Proposed as answer by Terry Xu - MSFT Thursday, November 30, 2017 6:03 AM
    Monday, November 27, 2017 10:20 PM