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]

Hello George,

To answer your question, tblfxparent.[datesettle] and tblfxrollschild.[datesettle] are different dates. I have the parent with a DateSettle and many childs with a DateSettle. I probably should have named the tblfxrollschild.DateSettle differently. The DateSettleC (C is for Child) should be the Max of the tblFXRollsChild.DateSettle, which is what I need a solution for.

Let me know if not clear.

[#3]

did you see my reply or you just ignoring it?

[#4]

@arnel_gp 

Hi Arnel,
Yes, I did see your response. I responded:
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" until I updated, which I think as George alluded to. I think a subquery is what I need since it works in the background without needing an event.

I am not sure how to use that update query inside of a query in the record source.  If I did, it seems that could be a workable solution since it would eliminate the need to fire an event.

What are your thoughts?  

[#5]

well, it is a response to your original OP, that you can't make the query run.
using subquery and domain function are the same, the data need to be saved first.

what you can do is Run the update query on the Open event of the Form.
then again on the AfterUpdate event. that will make your date updated.

[#6]

Arnel, as you should know by now, duplicating data and storing calculated values are sub-standard practices in a relational database application.

I admire both your skill with VBA code and your ability to interpret vague requirements and provide logical responses.

In this case, however, continuing to urge a work-around that preserves a sub-standard practice is actually counter-productive to the long-term interests of this poster. It is one thing to use code to implement important functions. That's a really valuable skill and you are very good at it. It is not useful to use that same skill to implement risky work-arounds.

[#7]

@arnel_gp 

I have decided to use the update query. I think I have the events covered in the form to ensure it updates when needed (On Open as you mentioned). I also have added it to a report's On Load (I also added it to the form's AfterUpdate). I turned off the warnings and back on too. I will only be using this DMax for a report and will hide the field on the form. I think I am good now. I have tested and it works.

If no objections, I will mark Arnel's as best response since I have ended up using the Dmax function.

thanks for the help guys.  I really appreciate it. 

[#8]

if mr.Tony will remove the other date (the one being updated).
he will still need to use DMax() or sub-query to get the "latest" date on a Query.
using Domain function is more flexible since the resulting Query is Always Updateable.

[#9]

@Tony2021 

I have had this same issue prepping data for processing hydrology simulations.

The fix I came up with is to simple create the select query to get your MIN/MAX values, run a Make Table query, then run an Update query from there.

I put all of mine in a macro that creates the tables and then deletes them after the updates are finished.

Not very technical, but it works.