Jan 04 2022 06:43 PM
Jan 04 2022 06:43 PM
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 not correct. I have one query inside of another and I thought this was the way to do what I want to do. As shown in the pic below, LetterOfCreditID is repeated and this is my problem. I only need the max of ID and 1 entry of the LetterOfCreditID (not repeating as it is now).
I hope it makes sense. I have attached a pared down db with only the 2 tables and 2 queries. I think it will make more sense by viewing. Let me know if there are any questions though. Thank you
Jan 05 2022 05:18 AM
Jan 05 2022 05:44 PM - edited Jan 05 2022 05:48 PM
HI Maria / George, thank you for the response. I appreciate it. Maria, I don't completely follow. I tried to implement but I am failing.
George: I dont think I can use a Max on the Balance since the values are not always increasing...they decrease sometimes so maybe a max would need to be on the letterOfCreditID. This is how I see it but when I put a max on the LetterOfCreditID it doesnt perform. Looking for some guidance.
Looking forward to your response.
Jan 05 2022 06:01 PM
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.
Jan 05 2022 06:51 PM
Jan 06 2022 12:16 AM - edited Jan 06 2022 12:16 AMSolution
you maybe right, see Query1 (also query qryLC_MaxDate).
Jan 06 2022 01:30 AM - edited Jan 06 2022 01:33 AM
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
Jan 06 2022 03:37 AM
Jan 06 2022 04:42 AM
Jan 06 2022 05:31 AM
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.