Using MAX in Update Query

[Tony2021]

Experts, I am building an update query.

I need to update tblFXParent to the MAX value of tblFXRollsChild].[DateSettle] where tblFXParent.IDFXParent=tblFXRollsChild.IDParentfk

I am getting an "aggregate function" error as below.  

What am I doing wrong? 

here is the SQL: 

UPDATE tblFXParent INNER JOIN tblFXRollsChild ON tblFXParent.IDFXParent = tblFXRollsChild.IDParentfk SET tblFXParent.DateSettleC = Max([tblFXRollsChild].[DateSettle])
WHERE (([IDFXParent]=[IDParentfk]));

[#2]

@Tony2021 

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.

[#3]

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.

[#4]

@Tony2021 

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

[#5]

@George Hepworth \

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;

[#6 · Accepted solution]

you may also try:

UPDATE tblFXParent Set DateSettleC = DMax("DateSettle","tblFXRollsChild","IDParentfk = " & [IDFXParent])

[#7]

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.

[#8]

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.

[#9]

Please show the actual SQL you wrote, so we can figure it out.

[#10]

@George Hepworth 

George, please see SQL below.  

I have also attached a sample db with both tables and the query.   You can see that if you change the Date tblRollsChild.DateSettle (where tblFXParent.[IDFXParent] = tblRollsChild.[IDParentfk]) then the qryMaxDtSettle.LastSettlementDate changes to NULL but if you change it back to that date you changed it from then the NULL changes back to what it was before.   Basically, if the date is changed, it updates to NULL.  I hope that makes sense.  

thank you very much. 

here is the SQL (but it is also in the attached db)

SELECT tblFXParent.*, [rate]*[AmountOrig] AS USD, Nz([AmountOrig],0)+Nz(DSum("AmountRoll","tblFXRollsChild","[IDParentfk]=" & [IDFXParent]),0) AS [Balance EURO], MaxDateSettle.LastSettlementDate
FROM tblFXParent LEFT JOIN (SELECT IDParentfk, Max(DateSettle) AS LastSettlementDate FROM tblFXRollsChild GROUP BY IDParentfk) AS MaxDateSettle ON (tblFXParent.[DateSettleC] = MaxDateSettle.[LastSettlementDate]) AND (tblFXParent.[IDFXParent] = MaxDateSettle.[IDParentfk])
ORDER BY tblFXParent.Vendor, tblFXParent.DateSettle;

[#11]

@Tony2021 

Why did you use a LEFT OUTER JOIN?

I thought you only wanted to see the MAX of the settlement dates in the child table?

[#12]

@George Hepworth 

Ah, I think I see now. You only have settlement dates for SOME of the parents. This query with the LEFT OUTER JOIN is intended to return ALL of the Parents, but with the max settlement date IF there is one for that parent in the Child table. So, this is more complicated. Give me a bit. Also, with this data, I'm sure someone else will be joining us...

[#13]

@Tony2021 

Well, if I am interpreting the requirement correctly, this version contains a modification to your query that should be very close to what you need, with one problem remaining.

It is a union of the SQL you had, except that it uses an INNER JOIN to match ONLY the max settlement date with the parent date, with a second SQL statement to return all of the other parent records which do NOT have a matching record in the child table. 

There is one record in the child table which appears to be a bogus record, or a record that doesn't match what we know so far about the tables.

If this is a legit record, we can make one further modification to the SQL to include it in the results. That's the second query in the attached.

[#14]

@George Hepworth 

Hi George,

thank you for the revised. 

I still seem to have that issue with the record though. 

If I change the tblFXRollsChild.DateSettle the qryMaxDtSettle as well as the other query will completely remove that ID but if I change it back then it reappears.  Not sure if you see the same on your end? 

Bogus Record:

Its not really a bogus record. I entered it but then backtracked but left the record since I know it will be populated very soon with data. It was a notification to self (I removed all of the comments and if I left them then it might have made more sense why the record was there.

I think this bogus type record will appear again though as I typically enter a record but the date is NULL.

==>I am am not sure if the calculation would need to be adjusted if there is a NULL for the date.  I assume not since I am needing the MAX.  

[#15]

@Tony2021 

I added a second query to handle that one record, it might not have made it into the zip file:

SELECT tblFXParent.*, [rate]*[AmountOrig] AS USD, Nz([AmountOrig],0)+Nz(DSum("AmountRoll","tblFXRollsChild","[IDParentfk]=" & [IDFXParent]),0) AS [Balance EURO], MaxDateSettle.LastSettlementDate
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])
UNION SELECT tblFXParent.*, [rate]*[AmountOrig] AS USD, Nz([AmountOrig],0)+Nz(DSum("AmountRoll","tblFXRollsChild","[IDParentfk]=" & [IDFXParent]),0) AS [Balance EURO], tblFXParent.IDFXParent
FROM tblFXParent
WHERE (((tblFXParent.IDFXParent) Not In (SELECT IDParentfk From tblFXRollsChild)))
UNION SELECT tblFXParent.*, [rate]*[AmountOrig] AS USD, Nz([AmountOrig],0)+Nz(DSum("AmountRoll","tblFXRollsChild","[IDParentfk]=" & [IDFXParent]),0) AS [Balance EURO], tblFXParent.IDFXParent
FROM tblFXParent
WHERE (((tblFXParent.IDFXParent) In (SELECT IDParentfk From tblFXRollsChild WHERE DateSettle IS NULL)))
ORDER BY Vendor, DateSettle;

[#16]

@George Hepworth 

George, I still have that issue. Just to be clear, I am referring to when I change the date that record disappears but when I change it back then it reappears (see comment in my previous note).

In the query above, I pasted into qryMaxDtSettle.
Let me know if yo8u are able to reproduce on your end.

Also, the the ID is being populated in the LastSettlement date field its seems?

[#17]

@Tony2021 

That could happen if the fields in the UNION query are not properly aligned. The exact same fields must be in the same position in each SQL Statement within the query, and there must be exactly the same fields. I see that I did overlook the presence of the additional field from the subquery for the MaxDateSettle.LastSettlementDate field because I had included an additional field for the foreign key in exactly that same position. I apologize for not analyzing the output properly. I was primarily looking at record count. Amend it like this.

SELECT tblfxparent.*,
[rate] * [amountorig] AS USD,
Nz([amountorig], 0)
+ Nz(Dsum("amountroll", "tblfxrollschild", "[idparentfk]=" & [idfxparent]), 0) AS [Balance EURO],
MaxDateSettle.lastsettlementdate
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] )
UNION
SELECT tblfxparent.*,
[rate] * [amountorig] AS USD,
Nz([amountorig], 0)
+ Nz(Dsum("amountroll", "tblfxrollschild", "[idparentfk]=" & [idfxparent] ), 0) AS [Balance EURO],
Null AS lastsettlementdate
FROM tblfxparent
WHERE (( ( tblfxparent.idfxparent ) NOT IN (SELECT idparentfk
FROM tblfxrollschild) ))
UNION
SELECT tblfxparent.*,
[rate] * [amountorig] AS USD,
Nz([amountorig], 0)
+ Nz(Dsum("amountroll", "tblfxrollschild", "[idparentfk]=" & [idfxparent]), 0) AS [Balance EURO],
Null AS lastsettlementdate
FROM tblfxparent
WHERE (( ( tblfxparent.idfxparent ) IN (SELECT idparentfk
FROM tblfxrollschild
WHERE datesettle IS NULL) ))
ORDER BY vendor,
datesettle;

The problem with date changes will have to wait I'm afraid. I've got a meeting starting in a few minutes.

[#18]

Quick thought before I duck back into my meeting. You may be running into another one of the problems of storing the same value in two places. I.e. you have two different values DateSettle in two tables. If they match, all is fine. If they don't match, you will see the problem you mention.

I wondered about that at the time, but was focused on the other issue of reporting only the Max settlement date from the child table. Let's return to the table design when I get a chance, maybe today but more likely tomorrow. In the meantime, lots of good Access developers are on line.

[#19]

@George Hepworth 

George,   That query looks very intimidating. Definitely expert level.
Do you think that the relationship might have something to do with the record disappearing?  thank you for your availability and kindness. Anytime is fine with me.  

GROUP BY idparentfk) AS MaxDateSettle
ON ( tblfxparent.[datesettlec] =
MaxDateSettle.[lastsettlementdate] )
AND ( tblfxparent.[idfxparent] = MaxDateSettle.[idparentfk] )

[#20]

Yes, it has everything to do with the record not being returned.
And, as I stated, once we got past the original problem, the table design problem became clear.
You already HAD two different fields in two tables containing settlement dates. That's a problem IF you expect them to stay in synch.
In my original response I indicated one of the reasons we don't store calculated values is the problem of keeping them current. This is another aspect of the same problem, i.e. the same value (settlement date) in two different places.

I originally, and without in-depth analysis, assumed that the two settlement dates (tblfxparent.[datesettlec] and tblfxrollschild.[datesettlec]) would be somehow independent. It turns out that they, too, are linked somehow.

So, the table design question is whether the settlement date even belongs in the parent table at all. In light of this discussion, I think it does not. I think the settlement dates are all in the child table and that is the ONLY place they need to be stored. And in that case, this whole problem simply goes away.

So, why does the same value -- settlement date -- appear in two places?