Forum Discussion
Balance with Max
you maybe right, see Query1 (also query qryLC_MaxDate).
Max() refers to the largest value in the CURRENT recordset, regardless of how that recordset is sorted.
Last() refers to the record in the final position in the CURRENT recordset, depending on how that recordset is sorted.
I take you to mean, therefore, that the size of the value is not what you mean by Last. What is you want is the MOST RECENTLY ENTERED record. You can't do that reliably by referring to the ID field, LetterOfCreditID. You have to select the MAX of the Date on which the LetterOfCredit record was entered.
Is that the right interpretation.
Just to clarify, for each letter of credit ID you want the maximum ID and the balance for the selected ID value.
If I've understood that correctly, it can be done in one query by using 2 copies of qryBalC as the record source and joining on the ID field. You should only group on one field: LetterOfCreditID
SELECT qryBalLC.letterofcreditID, Max(qryBalLC.ID) AS MaxOfID, Min(qryBalLC.Balance) AS MinOfBalance FROM qryBalLC AS qryBalLC_1 INNER JOIN qryBalLC ON qryBalLC_1.ID = qryBalLC.ID GROUP BY qryBalLC.letterofcreditID;NOTE: You have to use an aggregate function for the balance field but it doesn't have to be Max for this to work. I think Last would also give the same results IN THIS SCENARIO...but I usually avoid using Last for the reasons already stated
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.
