Forum Discussion
Using MAX in Update Query
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]));
- you may also try:
UPDATE tblFXParent Set DateSettleC = DMax("DateSettle","tblFXRollsChild","IDParentfk = " & [IDFXParent])
27 Replies
- did you see my reply or you just ignoring it?
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?
- 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.
- 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
