Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Oct 01, 2022

Update Query

Hello experts, I have an update query SQL below and I am getting an error message.  Do you see anything obviously wrong with it? I am not sure why I am getting that error msg in the screen shot. 

 

UPDATE tblFXParent INNER JOIN qryFXSettDtCLast ON tblFXParent.IDFXParent = qryFXSettDtCLast.IDParentfk SET tblFXParent.DateSettleC = [qryFXSettDtCLast].[LastOfDateSettle];

 

thank you

 

 

 

 

access
  • arnel_gp's avatar
    arnel_gp
    Oct 02, 2022
    UPDATE tblFXParent SET DateSettleC = DLOOKUP("LastOfDateSettle", "qryFXSettDtCLast", "IDParentfk = " & [IDFXParent])
  • XPS35's avatar
    XPS35
    Iron Contributor
    Oct 01, 2022
    I guess qryFXSettDtCLast is not updateable (looks like an aggregate qeury). That will make your update query not updateable.
    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Oct 01, 2022

      XPS35 

       

      Ok that makes sense. qryFXSettDtCLast is an aggregate query. Do you happen to know of a workaround? I definitely need to update tblFXParent.DateSettleC to the LAST of Date Settle as found in [qryFXSettDtCLast]

      If it helps, below is my sql of qryFXSettDtCLast
      SELECT tblFXRollsChild.IDParentfk, Last(tblFXRollsChild.DateSettle) AS LastOfDateSettle
      FROM tblFXRollsChild
      GROUP BY tblFXRollsChild.IDParentfk;

      • XPS35's avatar
        XPS35
        Iron Contributor
        Oct 01, 2022

        Tony2021 I am working on a tablet (Android} now and need to try some things first. I will be back later. 

  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    Oct 02, 2022
    UPDATE tblFXParent SET DateSettleC = DLOOKUP("LastOfDateSettle", "qryFXSettDtCLast", "IDParentfk = " & [IDFXParent])