M
MF Scheetz
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
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