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

[matatooie]

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.

[Rabbit]

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

[matatooie]

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

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

[Rabbit]

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.

[MMcCarthy]

Try this ...

UPDATE tblProvider INNER JOIN tblCallLog 
ON tblCallLog.prov_id=tblProvider.jsi_id
SET tblProvider.Inst_id="18"
WHERE tblCallLog.Inst_id="18"

Mary