Balance with Max

[Tony2021]

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

[#2]

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.

[#3]

@Tony2021 

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.

[#4]

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.

[#5]

@Tony2021 

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.

[#6]

George, I think the Max of the date could work since I have Not Is Null criteria on [DateRecBeneConsent]. I think I referred to only Max. I have never used Last. I am open to anything that works though.

[#7 · Accepted solution]

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

[#8]

@Tony2021 

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

[#9]

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.

[#10]

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.

[#11]

@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.