help on update query...

  • Thread starter Thread starter kumag
  • Start date Start date
K

kumag

hello people, need your help on this update query I made.

update LAB_ANALYSIS
set LAB_ANALYSIS.MLF_COST =
(
SELECT MLF_TESTCOST.CostPerTest
FROM (MLF_TESTCOST INNER JOIN METHODS ON MLF_TESTCOST.TestName =
METHODS.MLF_TEST)
WHERE METHODS.MethodName = LAB_ANALYSIS.REQUEST_ANALYSIS
)
where LAB_ANALYSIS.WORK_ID IN
(
SELECT LAB_WORK.WORK_ID FROM LAB_WORK
WHERE LAB_WORK.DATE_RECEIVED >= 3/1/2006
AND LAB_WORK.DATE_RECEIVED <= 3/31/2006
)

it's giving up this error when i try to run it:

Operation must use an updateable query

thanks a lot =)
 
try

UPDATE
LAB_ANALYSIS As LA
INNER JOIN
LAB_WORK As LW
ON LA.WORK_ID = LW.WORK_ID
SET LA.MLF_COST =
(
SELECT First(MLF.CostPerTest)
FROM
MLF_TESTCOST As MLF
INNER JOIN
METHODS AS M
ON MLF.TestName = M.MLF_TEST
WHERE
M.MethodName = LA.REQUEST_ANALYSIS
)
WHERE
LW.DATE_RECEIVED >= 3/1/2006
AND
LW.DATE_RECEIVED <= 3/31/2006
Click to expand...
which of course will not work because
you cannot use aggregate anywhere in
update query!

if you can always expect only one
CostPerTest value to be returned
from subquery, remove "First"

else,
use domain function in place of
subquery if more than one value
exists but they are all the same

ie., save a query (say "qryCost")
SELECT MLF.CostPerTest, M.MethodName
FROM
MLF_TESTCOST As MLF
INNER JOIN
METHODS AS M
ON MLF.TestName = M.MLF_TEST
Click to expand...

then SET stmt would be something like
SET LA.MLF_COST =
Click to expand...
DMax("CostPerTest","qryCost","[MethodName]='"
& LA.REQUEST_ANALYSIS & "'")


else,
which one of those values did
you want to use?
 
thanks Gary, after I made this post I gave one last shot in
testing/fixing my problem and thankfully it worked. with help of
previous posts in this group, I was able to make it work.

seems access doesn't like select statements when setting values (i.e.
"set _field0 = select...) even if it does return only one record. so i
tried placing my joins in the update sec tion (i.e. update table1 join
table2 join...) and thankfully it worked.

i don't really program in access that much, only if something comes up
(like fixing/extending legacy systems) so this was kinda new to me.

thanks again for your response, much appreciated =)
 
Back
Top