Stuck with Update of Query to a Table

[FuzMic]

Hi

I have a select query with Sum of a numeric field, let say we name Query as QTot:
SELECT ID1, Sum(Num) AS SNum FROM TB1 GROUP BY ID;


Then i wanted to use these Sum ie SNum to update a table Tb2, i use

UPDATE Tb2 INNER JOIN ON TB1.ID1 = TB2.ID2 SET Tb2.Val = [QTot].[SNum] WHERE [Tb2].[Flag]="AFlg";

It cannot work, it says that it is not an updatable Query; how?

 

[June7]

Any UPDATE that involves an aggregate query will not work. There seldom is need to save aggregate data and it can be dangerous - saved data can become 'out of sync' with raw data. Calculate when needed. You already have the calculation, just display it don't save it.

 

[FuzMic]

June thanks but the snapshot of the aggregate is static & still want to update the 2nd table. I can use Adodb record recordset to do it

 

[June7]

Not sure what you mean by aggregate is static. Since data is continually added and edited, how can aggregate calculation be static?

Yes, can loop through recordset in VBA and save values to table. Cannot run Access query object to accomplish.

Still advise this is not necessary.

 

[June7]

If you want to implement option 2, recommend instead of MakeTable which modifies db design, create a permanent table and append records then use it in the UPDATE. Then purge records at end of process. This is a 'temp' table - table is permanent and records are temporary.

 

[arnelgp]

you can create this function in a Module:

Public Function fnSum(sTable As String, sPK As String, sPKValue As Variant, sFieldToSum As String) As Variant
    sPK = Replace(Replace("[" & sPK & "]", "]]", "]"), "[[", "[")
    sTable = Replace(Replace("[" & sTable & "]", "]]", "]"), "[[", "[")
    sFieldToSum = Replace(Replace("[" & sFieldToSum & "]", "]]", "]"), "[[", "[")
    
    With CurrentDb.CreateQueryDef(vbNullString, _
        "SELECT SUM(" & sFieldToSum & ") AS expr1 FROM " & sTable & " " & _
        "WHERE " & sPK & "=@1;")
        .Parameters(0) = sPKValue
        fnSum = .OpenRecordset(dbOpenSnapshot)(0)
    End With

End Function

on your query:

UPDATE Tb2 SET Tb2.Val = fnSum('tb1', 'id1', [id2], 'num') WHERE [Tb2].[Flag]="AFlg";

 

[FuzMic]

Thanks Ladies & Gentleman, all feedback much appreciated.

Pat I am waiting for the avalanche in the US midterm elections on your 435 seats in the House of Representatives and 35 in Senate; a little politics in this forum that our world really need and to be thankful for. .. Counting down.