Forum Discussion
Using MAX in Update Query
- you may also try:
UPDATE tblFXParent Set DateSettleC = DMax("DateSettle","tblFXRollsChild","IDParentfk = " & [IDFXParent])
Actually, I think there are a couple of different kinds of problems.
First, attempting to update the field this way is not an appropriate approach in an Access relational database application. We do not recommend storing calculated values, such as this. For one thing, they run the risk of going out of synch because there's nothing to prevent a user from adding a new record in the tblFXRollsChild with a later date, which may or may not get updated again in the parent table. Risk of data integrity problems is one reason we seldom store calculated values.
Second, it can't be done with this syntax. I'm hesitating between saying, "just don't do it", and offering an example anyway.
- Hi George, thanks for the info. I initially used an unbound field and made a Dlookup but it didnt update very quickly and so the calculated field was my plan B. I now seem to remember someone saying that storing calculated fields is not the best method.
Could I ask what other ideas do you have? thank you.Typically, this is done with a subquery to find the Max(Value) in the child table. Here that would be the Max date for each instance of IDParentfk, which links the records to the IDFXParent Primary Key:
SELECT IDParentfk, Max(DateSettle) AS LastSettlementDate
FROM tblFXRollsChild
GROUP BY IDParentfk
Then you can join this as a subquery in a SELECT query on the Parent table.
SELECT IDFXParent, LastSettlementDate
FROM tblFXParent INNER JOIN (
SELECT IDParentfk, Max(DateSettle) AS LastSettlementDate
FROM tblFXRollsChild
GROUP BY IDParentfk) AS MaxDates ON tblFXParent.IDFXParent = MaxDate.IDParentfk
ORDER BY IDFXParent
HI George, thank you very much for the guidance. Queries alone are hard enough and queries in queries are diabolically confusing to me but I think I can do it.
Question:
I have a field named tblFXParent.[DateSettleC].
Is LastSettlementDate to be this field? Meaning that I should change LastSettlementDate to DateSettleC? I assume this is the case but wanted to make sure.Below is my sql if it helps.
All fields are denoted by the * so tblFXParent.[DateSettleC] is not showing. Note that I have 2 fields similarly named: tblFXParent.[DateSettleC] and tblFXParent.DateSettle (no "C")
SELECT tblFXParent.*, [rate]*[AmountOrig] AS USD, Nz([AmountOrig],0)+Nz(DSum("AmountRoll","tblFXRollsChild","[IDParentfk]=" & [IDFXParent]),0) AS [Balance EURO],
FROM tblFXParent
WHERE (((tblFXParent.AmountOrig) Like "*" & [Forms]![frmFXTracker]![txEndUser] & "*"))
ORDER BY tblFXParent.Vendor, tblFXParent.DateSettle;
