Forum Discussion
Using MAX in Update Query
- you may also try:
UPDATE tblFXParent Set DateSettleC = DMax("DateSettle","tblFXRollsChild","IDParentfk = " & [IDFXParent])
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;- Hmmm. Significantly more complex than the original....
Still the same principle applies. Join in the subquery on the Max(DateSettleC).
Try this, but beware it's hard to test without data.
SELECT tblFXParent.*
, [rate]*[AmountOrig] AS USD
, Nz([AmountOrig],0)+Nz(DSum("AmountRoll","tblFXRollsChild","[IDParentfk]=" & [IDFXParent]),0) AS [Balance EURO]
FROM tblFXParent
INNER JOIN (
SELECT IDParentfk
, Max(DateSettle) AS LastSettlementDate
FROM tblFXRollsChild
GROUP BY IDParentfk) AS MaxDateSettle
ON tblFXParent.[DateSettleC] = MaxDateSettle.[LastSettlementDate]
AND tblFXParent.[IDFXParent] = MaxDateSettle.[IDParentfk]
WHERE (((tblFXParent.AmountOrig) Like "*" & [Forms]![frmFXTracker]![txEndUser] & "*"))
ORDER BY tblFXParent.Vendor, tblFXParent.DateSettle;
NOTE: In my first post, I did miss the required join on the parentID as well as the settlement date.
Further note: While it is possible to store calculated values, most professional Database applications do it only when circumstances are appropriate. This case exposes why it's not desirable. Every time a new record, with a later DateSettle value is added in the child table, the calculated value in the other table goes bad. And we prefer not to store bad data. The work-around that would be to continually massage data hoping to catch such problems. Not an ideal use of anyone's time and talent.- HI George,
I am now using the subquery. I am making progress.
Question: If I change the date in tblFXRollsChild.DateSettle it doesnt seem to change in the LastSettlementDate in the query. What it does is if I change the tblFXRollsChild.DateSettle to some other date and refresh, the LastSettlementDate then changes from the original date to a NULL but if I change it back to the original date as it was and I refresh, the LastSettlementDate changes back to the original date. I have tested a few records. I can assure its not a refresh issue as I have closed out any forms and reopened. I imagine I am doing something wrong. Let me know what you think when you have a sec. thank you
Arnel: the update query worked and I compared it to George's output and they matched. I then thought I would have to fire it off many times to keep the data current, by firing an event but I dont thk that is a catch all so some data would be I think "dirty" I think as George alluded to. I think a subquery is what I need since it works in the background without needing an event.