Update table based upon join

  • Thread starter Thread starter H0MELY
  • Start date Start date
H

H0MELY

Thank you all for looking, I very much appreciate any suggestions. I have a
database that is has a summary table that has a collection of data about
Telephones and their service (i.e. ID, make, model, location, responsible
party, classification and comments). Basically what I need to do is run an
update query that will update the comments based upon what is in the
classification field of the summary table and the ID of a second table (it is
actually a select query if that makes a difference).

What is happening is that all of the reporting queries that have been built
and distributed get their data from the summary table...and it was decided
that the users want the comments reformatted to include an extra piece of
information...but only for the phones with matching IDs from the query (I
hope this makes sense).

The easiest thing I am hoping is to run an update query based upon a join
and the ID field in the query...but it isn't working. I am probably just
missing something but any suggestions or assistance would be much
appreciated. Thank you all for looking.

-John
 
The easiest thing I am hoping is to run an update query based upon a join
and the ID field in the query...but it isn't working. I am probably just
missing something but any suggestions or assistance would be much
appreciated. Thank you all for looking.
Click to expand...

Fix the error in the query then.

We can't see it from here. Please open the query in SQL view and post the SQL
text here; also give some sort of indication what "isn't working". "Doctor, I
don't feel good, what should I take" doesn't give much to go on for a
diagnosis!
 
Sorry that I wasn't clear. Here is the SQL of what I tried...

UPDATE tbl_service_changes INNER JOIN Query7 ON tbl_service_changes.atm_id =
Query7.atm_id SET tbl_service_changes.Comments = "change"
WHERE (((tbl_service_changes.Classification)="Service Lost"));

When I try and run it I get a msg box that reads...

"Operation must use an updateable query."

Any ideas on how to fix this so that it will update the comments only for he
atm_ids listed in query 7?

Thank you for your help.

-John
 
HA...I just tried something, I turned Query 7 into a make table query and
then ran the update query from teh table...what do you know, it worked.
Thank you again for your help but I just found my solution.
 
HA...I just tried something, I turned Query 7 into a make table query and
then ran the update query from teh table...what do you know, it worked.
Thank you again for your help but I just found my solution.
Click to expand...

Well... it's *A* solution but probably not a very good one, especially if this
will be a repeated action. MakeTable queries bloat your database and require
that the "made" table be deleted or renamed prior to running the maketable
query a second time.

I think you can make your query updateable. If the Join doesn't work, often a
Subquery will. Try

UPDATE tbl_service_changes
SET tbl_service_changes.Comments = "change"
WHERE (((tbl_service_changes.Classification)="Service Lost"))
AND ID IN(SELECT atm_id FROM Query7);

Note that this will overwrite and destroy any existing Comments; I hope that's
what you intend to do!
 
Back
Top