Update Queries.

[Guest]

Hello

I am trying to update one table with values from a group/sum query as follows

I have Table1 containing a text and a ID field, having a one-to-many relation to Table2, containing ID and a value
I write a query to calculate the sum of the value (Query1)

SELECT Table2.ID, Sum(Table2.Value) AS VALUE FROM TABLE2
UPDATE Table1 INNER JOIN Query1 ON Table1.ID SET Table1.Text = "Value: " & Query1.VALUE

However, since Query1 is a result of group/sum, this query is (quite correct) not updatable. Joining with Table1 causes the resulting table to be not updatable. My only option seems to do a DLookup() of the value, but that is painfully slow

If anyone know how to do this update in a faster way, please help me out

Regards
Ivar Svendsen

 

[John Viescas]

The only way to make it faster is to insert the results from Query 1 into a
table that has ID as its primary key. Use the table in the Update Join.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hello.

I am trying to update one table with values from a group/sum query as follows:

I have Table1 containing a text and a ID field, having a one-to-many

relation to Table2, containing ID and a value.

I write a query to calculate the sum of the value (Query1):

SELECT Table2.ID, Sum(Table2.Value) AS VALUE FROM TABLE2;
UPDATE Table1 INNER JOIN Query1 ON Table1.ID SET Table1.Text = "Value: " & Query1.VALUE;

However, since Query1 is a result of group/sum, this query is (quite

correct) not updatable. Joining with Table1 causes the resulting table to be
not updatable. My only option seems to do a DLookup() of the value, but that
is painfully slow.