Forum Discussion
Balance with Max
you maybe right, see Query1 (also query qryLC_MaxDate).
you maybe right, see Query1 (also query qryLC_MaxDate).
- HI Arnel, thank you very much! I have looked it over and its perfect. These types of queries are so difficult for me. Sincerely appreciate. Thank you to all. I dont clearly understand why I can not use Max on the autonumber as you detailed and instead better to use the max of the date field. I assume there could be a case that the data is not accurate if I were to use the Max on the autonumber.
The Autonumber is guaranteed ONLY to be unique. It is sequential and incrementing for the most part, but there is no guarantee that the sequence will always match the required order.
For example, you enter a record at 8:00 AM and leave your desk for a while. A new item is placed on your desk in your absence. It is an item that SHOULD have been entered the day before. You enter it at 9:00 AM. Or someone discovers an error and deletes a record from two days ago, and then reenters it correctly, retaining the original date. Now you have a situation where the dates are in one sequence and the Autonumbers in the opposite sequence. Rare, but not impossible.That is why most experienced Access developers prefer Max() over Last() and only use fields that they KNOW will be properly sequential. The Max() date will return the correct record regardless of sort order. To get the correct record using Last() date, you have to sort the recordset first, using date.
