Hi, I am new to creating db statements in Access 2000 and would appreciate your feedback.
I have three tables:
ORIG_SHIPS
id
vessel type
MVMT_TYPE
mvmt_type_id
mvmt_type_abbrev
MOVEMENT
mvmt_id
mvmt_type_id
In my database, movement.mvmt_type_id is currently null in all rows.
I would like to update the value of movement.mvmt_type_id
with the value of mvmt_type.mvmt_type_id,
where movement.mvmt_id = orig_ships.id
and orig_ships.vessel_type = mvmt_type.mvmt_type_abbrev.
I have tried and tried several SQL statements without success. I may be way off base, but my latest attempt reads like this...
update movement set mvmt_type_id = (select mvmt_type.mvmt_type_id from orig_ships, mvmt_type
where orig_ships.vessel_type = mvmt_type.mvmt_type_abbrev);
I get an error message
"operation must use an updatable query".
Can you suggest a better approach than the update statement I have written?
I have three tables:
ORIG_SHIPS
id
vessel type
MVMT_TYPE
mvmt_type_id
mvmt_type_abbrev
MOVEMENT
mvmt_id
mvmt_type_id
In my database, movement.mvmt_type_id is currently null in all rows.
I would like to update the value of movement.mvmt_type_id
with the value of mvmt_type.mvmt_type_id,
where movement.mvmt_id = orig_ships.id
and orig_ships.vessel_type = mvmt_type.mvmt_type_abbrev.
I have tried and tried several SQL statements without success. I may be way off base, but my latest attempt reads like this...
update movement set mvmt_type_id = (select mvmt_type.mvmt_type_id from orig_ships, mvmt_type
where orig_ships.vessel_type = mvmt_type.mvmt_type_abbrev);
I get an error message
"operation must use an updatable query".
Can you suggest a better approach than the update statement I have written?