table join with a sum works in sql
Hi,
Jet doesn't like aggregate in updateable query. Use a temp table or, if
within the combo JET+ACCESS:
UPDATE table1 SET col3= DSum("col2", "table2", "col1=" & Table1.Col1)
If it was not of the SUM, you could have write a JOIN rather than a
sub-select statement, and Jet will have agree to update ( I use your Oracle
statement, dropping SUM around table2.col3, then, in Jet, it would have be):
UPDATE table1 INNER JOIN table2
ON table1.col1=table2.col1
SET table1.col3= table2.col3
Generally, Jet prefers JOIN over WHERE clause involving two tables (as long
as updateability is concerned).
Hoping it may help,
Vanderghast, Access MVP
Quote:
> here is an oracle statement converted for access
> UPDATE table1 SET col2 = (SELECT col2 FROM table2
> WHERE table1.col1 = table2.col1 AND table1.col3 = table2.col3);
> CONVERTED TO
> strSQl = "UPDATE table1 LEFT JOIN table2 " & _
> "ON table1.col1 = table2.col1 " & _
> "SET table1.col2 = table2.col2;"
> I do not know how to work the group function
> sum into access
> this oracle sql statement
> UPDATE TABLE1 SET COL3 =
> (SELECT SUM(TABLE2.COL3) FROM TABLE2
> WHERE TABLE1.COL1 = TABLE2.COL1);
> needs to be usable in access
