Forum Discussion
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 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
you maybe right, see Query1 (also query qryLC_MaxDate).
- The approach you should take is a two step approach. In the first query, leave out the Balance and just group by letterofcreditID. Then join that query to your qrybalLC query on MAX(qryBallLC.ID) = ID to get the correct Balance and the ID for the letterofcreditID.
I may misunderstand, but here's how I would think you want it. Note that my proposal is the same as the approach Maria suggested.
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.
thank you.
Looking forward to your response.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.
- you don't want to use the autonumber ID on the max() function.
autonumber can Guarantee the Uniqueness of record, but not it's Sequence.
it can even have Negative value and what else.
so your best field to use Max is on the Date field.
