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
On Mon, 25 Aug 2008 15:18:15 -0700, H0MELY wrote: >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. 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! -- John W. Vinson [MVP]
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 "John W. Vinson" wrote: > On Mon, 25 Aug 2008 15:18:15 -0700, H0MELY > wrote: > > >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. > > 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! > -- > > John W. Vinson [MVP] >
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. "John W. Vinson" wrote: > On Mon, 25 Aug 2008 15:18:15 -0700, H0MELY > wrote: > > >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. > > 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! > -- > > John W. Vinson [MVP] >
On Tue, 26 Aug 2008 06:55:02 -0700, H0MELY wrote: >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. 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! -- John W. Vinson [MVP]
