Stuck with Update of Query to a Table (1 Viewer)

FuzMic · Oct 30, 2018

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 · Oct 30, 2018

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 · Oct 30, 2018

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 · Oct 30, 2018

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.

Pat Hartman · Oct 30, 2018

You can use an append query if you want to store data at a point in time. So, for example, you can calculate a balance at the end of the month and then append that balance to the history table. But, if you want to update an existing row, you have two choices.
1. VBA to loop through the aggregated query and update the permanent table.
2. Run the aggregated query as a make table and then use the temp table rather than the query to join to the permanent table to update it.

I would really try to work with a query against live data rather than storing the calculated value. Stored calculations are always suspect when the live data can be changed. For example, if you calculate the balance before start of business on the first of each month, what happens if some updates didn't get applied and they happen on the second? If you look at the balance in the history table, it will be different than what you would calculate from the active table.

June7 · Oct 30, 2018

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 · Oct 30, 2018

you can create this function in a Module:

Code:

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 · Oct 31, 2018

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.

Pat Hartman · Nov 5, 2018

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.

June, there is no effective difference. Both methods bloat the database and so both are bad.

Pat Hartman · Nov 5, 2018

FuzMic,
We will all be glued to the boob tube Tuesday evening. The result will be higher taxes, Impeach, Impeach, Impeach alternating with "off with his head", and more trade deals that give away the farm OR more tax reform, better health care solutions (Trump's changes are already saving my daughter $500 per month), real border control, a fix for the illegal immigration problem which our government has been systematically lying about for years as new estimates indicate (not 11 million but more like 22-30 million), a real end to the Korean war along with nuclear disarmament of N. Korea and possibly even reunification of the Korean Peninsula, not to mention actual peace in the Middle East.

Stuck with Update of Query to a Table (1 Viewer)

FuzMic

DataBase Tinker

June7

AWF VIP

FuzMic

DataBase Tinker

June7

AWF VIP

Pat Hartman

Super Moderator

June7

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

FuzMic

DataBase Tinker

Pat Hartman

Super Moderator

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread