I can't get this query to work....

I'm running Access 2003 on XP and I am trying to do something that can't be done. I'm a total access plebe. The query I'm trying to run is:

UPDATE tblProvider
SET tblProvider.Inst_id=(SELECT tblCallLog.Inst_id
FROM tblCallLog
WHERE (((tblCallLog.Inst_id)="18") AND ((tblCallLog.prov_id)=(tblProvider.jsi_id))))
WHERE EXISTS
(SELECT tblCallLog.Inst_id
FROM tblCallLog
WHERE (((tblCallLog.Inst_id)="18") AND ((tblCallLog.prov_id)=(tblProvider.jsi_id))));

Basically, if the institution ID is 18 in the tblCallLog AND the tblCallLog.prov_id and tblProvider.jsi_id are equal, I want to change the tblProvider.Inst_id to 18.

Can this be done? I am having no luck here.

re: I can't get this query to work....

I think you'll have to fall back on DLookup. I think subqueries would make this an unupdateable query.

re: I can't get this query to work....

I was afraid of that.... Let me say for the record I hate access.

re: I can't get this query to work....

Looks like I spoke too soon.

I believe you can do this without a subquery. Try INNER JOINing the tables on the id field and set up the criteria appropriately.

re: I can't get this query to work....

Try this ...
Mary