By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

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

P: 2
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.
May 7 '07 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,366
I think you'll have to fall back on DLookup. I think subqueries would make this an unupdateable query.
May 7 '07 #2

P: 2
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.
May 7 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
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.
May 7 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblProvider INNER JOIN tblCallLog 
  2. ON tblCallLog.prov_id=tblProvider.jsi_id
  3. SET tblProvider.Inst_id="18"
  4. WHERE tblCallLog.Inst_id="18"
Mary
May 8 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.