Self-Join Update Query

[Guest]

I have created a Self Join Query:
SELECT DISTINCT A.txtLoanNo, A.intDR, A.intActExp/B.ActSum AS Perc
FROM tblDR AS A INNER JOIN
(SELECT txtLoanNo, Year(dteBegin) AS BgtYr, Sum(intActExp) AS ActSum
FROM tblDR
GROUP BY txtLoanNo, Year(dteBegin)
HAVING Year(dteBegin) Is Not Null AND Sum(intActExp)>0) AS B
ON A.txtLoanNo = B.txtLoanNo
AND A.dteBegin Like "*" & B.BgtYr;

This works great!

Now my problem is making an update query where A.intPerc is update with the
value "Perc" from the above query.

I've tried:
UPDATE tblDR AS A INNER JOIN
(SELECT txtLoanNo, Year(dteBegin) AS BgtYr, Sum(intActExp) AS ActSum
FROM tblDR
GROUP BY txtLoanNo, Year(dteBegin)
HAVING Year(dteBegin) Is Not Null AND Sum(intActExp)>0) AS B
ON A.txtLoanNo = B.txtLoanNo
AND A.dteBegin Like "*" & B.BgtYr
SET A.intPerc = A.intActExp / B.ActSum;

But I get "Operation must use and updateable query".

Any help is greatly appreciated.

-Matt

 

[Michel Walsh]

You are using GROUP BY and aggregate, SUM, which mark your query as not
updateable.

Push your subquery into a temporary table and use that table in your update
query.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel,

Thanks alot. That was actually the way I had set it up. I just didn't know
if I could run it with out a temp table.

-Matt