A Questions on update query.

[Trinidad]

Hi
I do have a question about a query on update. I appreciate if anyone could
help me.

Here is the table I have:


Emp

ID Lname Fname OfficeID
1 A Bobo 1
2 B Coco 2
3 C Jojo 1

Office
ID Name StateID
1 HQ 1
2 WareHouse 2

State
ID Name
1 WA
2 CA

I want to move the HQ from WA to NY.
First I do this:
insert into state
(Name)
values ('NY');

Then, I do this:
Update office a
set a.StateID = (Select b.ID from State b where b.Name = 'NY')
where a.Name = 'HQ';

Access complains: "Operation must use an updateable query."

How could I fix that? Or Access can't do that, I have to do this on SQL?

Thanks.



Thanks.

 

[Duane Hookom]

Update office
set StateID = DLookup("ID", "State", "[Name] = 'NY'")
where [Name] = 'HQ';

 

[Marshall Barton]

I do have a question about a query on update. I appreciate if anyone could
help me.

Here is the table I have:


Emp

ID Lname Fname OfficeID
1 A Bobo 1
2 B Coco 2
3 C Jojo 1

Office
ID Name StateID
1 HQ 1
2 WareHouse 2

State
ID Name
1 WA
2 CA

I want to move the HQ from WA to NY.
First I do this:
insert into state
(Name)
values ('NY');

Then, I do this:
Update office a
set a.StateID = (Select b.ID from State b where b.Name = 'NY')
where a.Name = 'HQ';

Access complains: "Operation must use an updateable query."

The subquery throws Access off its feed, use DLookup
instead:

Set a.StateID = DLookup("ID", "State", "[Name] = 'NY' ")

 

[Trinidad]

Thanks.
So, my query basically vaild but just I should use dlookup in Access?

I do have a question about a query on update. I appreciate if anyone could
help me.

Here is the table I have:


Emp

ID Lname Fname OfficeID
1 A Bobo 1
2 B Coco 2
3 C Jojo 1

Office
ID Name StateID
1 HQ 1
2 WareHouse 2

State
ID Name
1 WA
2 CA

I want to move the HQ from WA to NY.
First I do this:
insert into state
(Name)
values ('NY');

Then, I do this:
Update office a
set a.StateID = (Select b.ID from State b where b.Name = 'NY')
where a.Name = 'HQ';

Access complains: "Operation must use an updateable query."

The subquery throws Access off its feed, use DLookup
instead:

Set a.StateID = DLookup("ID", "State", "[Name] = 'NY' ")

 

[Marshall Barton]

In the restricted situation of an UPDATE query, that does
seem to be the case. Why using a subquery in an Update
query should make it not updatable is beyond me. It is a
definite annoyance, but what can we do but find a way around
it?
--
Marsh
MVP [MS Access]

So, my query basically vaild but just I should use dlookup in Access?

Trinidad wrote: []

Then, I do this:
Update office a
set a.StateID = (Select b.ID from State b where b.Name = 'NY')
where a.Name = 'HQ';

Access complains: "Operation must use an updateable query."

"Marshall Barton"wrote:
The subquery throws Access off its feed, use DLookup
instead:

Set a.StateID = DLookup("ID", "State", "[Name] = 'NY' ")