Sorting order and First() function

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?

Answer 1

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

Answer 2

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

Answer 3

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