People can look at a series, or list, of values and decide which one is "the first" and which one is "the last" by referencing both the values themselves and the context in which the question was asked.
Perhaps such a question might be "Of this list of people and their birthdates, which birthdate came first?"
We don't need to have the birthdays sorted in date order to answer that question because our minds are trained to recognize the context as well as the values. For example, if the list of birthdates is presented to us with the names of the people sorted alphabetically, we don't go to the item at the top of the list and pick it because know that's not the right context; we scan the entire list looking for the earliest one relative to other birthdates.
That kind of disambiguation is a skill computers are still learning, and one which Relational Database Applications are not set up to handle directly. We still need to make explicit the Sort Order the computer must use in order to find "the first".
Put it a different way.
If the question is phrased as it is above, the sort must be "earliest" birthdate to "most recent" birthdate so that the "earliest" is also "first" on the resulting set of records.
If the question is phrased differently, the sort must be different. "Of this list of people and their birthdates, which one is the birthdate of the first person on the list?"
To answer that question, of course, we have to know which sort order to apply to the list to get the result expected by the questioner. Do they want the "first" person alphabetically? Alphabetically by FirstName? Alphabetically by LastName? Or does "first" refer to some other criteria like "tallest", "top producer", etc?
When the question is asked explicitly, it's easy to see why and how the answer will be different. We're asking about birthdates in different contexts, and that means first doesn't always return the same answer.
Because of that ambiguity, many seasoned Access developers prefer to use Min() and Max() instead of First() and Last(). Min() and Max() are absolutes within the values themselves, regardless of context. The Minimum birthdate is the same regardless of any sort applied any of the other fields in that query. The same is true for Maximum birthdate. Access doesn't need to disambiguate the contextual reference for the question, "What is the minimum (or earliest) birthdate in this list of people and their birthdates?"
While it is possible to get accurate results when using First() and Last(), doing so depends on knowing both the values and the context in which the comparison is being made.
If you want to continue to use First() and Last() instead of Min() and Max(), continue to do so. Just keep in mind the possibility of potential subtle errors and make sure you use the appropriate ORDER BY clause.
 | |
| First and Last refer to position in a list. Min and Max refer to values in one field. |
