Unanswered: last() doesn't return last value of query
i have a rough and ready query set so i can quickly find Id numbers not being used,
SELECT Last(Val([StudentId])) AS Id
GROUP BY stud_admin_NSTUPERSONAL.SetId, stud_admin_NSTUPERSONAL.LeftSchool
HAVING (((stud_admin_NSTUPERSONAL.SetId)="Current Ye") AND ((stud_admin_NSTUPERSONAL.LeftSchool)="n"))
ORDER BY Last(Val([StudentId])) DESC;
'Current Ye' is a dataset and must be defined.
im having to chane the id to a number using val as our software stores ID's as text!?!?!?!?!
the result im getting is:
wheras the last 4 values of my query when its not got the last function are:
why is the query returning 4017 as the last in a list of values that goes to 4090.
of course! (slaps forehead!) last is for text strings right?
Not quite. Last() is a somewhat contentious aggregate function. It is specific to Access (to my knowledge) and does not comply with the ANSI SQL standards.
It returns the "last" created record (based on the grouping, where and having options you enter). So - if the student record for 4017 was created after the records for 4090, 4089 etc then this is the record returned.
The reason Last() is a little controversial is that the order of the rows in a table has no logical meaning. Since everything is about the key, the whole key and nothing but the key each row is discrete and there is no relationship to other rows in the table other than that they describe similar entities. One entity is not really "before" or "after" any other. But they are in Access