Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Jan 04, 2022
Solved

Balance with Max

Hello Experts,   I have a simple query and I need to show the tblLetterOfCredit.[Balance] based on the MAX of qryBalLC.ID.  I have been fiddling around with it for awhile and I guess my approach is...
access
  • arnel_gp's avatar
    arnel_gp
    Jan 06, 2022

    you maybe right, see Query1 (also query qryLC_MaxDate).

Tony2021's avatar
Tony2021
Steel Contributor
Jan 06, 2022
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.
George_Hepworth's avatar
George_Hepworth
Silver Contributor
Jan 06, 2022

Tony2021 

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.