 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008update 4 columns in one query?
Hello i want to update 4 different columns using just one query, at present i use 4 different queries one for each column which works fine. however when i tried to put them all in one update query it failed. please help Thanks Paul It would help if you posted the SQL of the relevant queries. We can't see what you are doing or what you are attempting. Normally, it is not a problem to update many fields in one table. The simplest such query might look like the following. UPDATE SomeTable SET Afield = 1 , BField = "Saturday" , Cfield = #1/1/2008# , DField = [zField] (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > Hello > [quoted text clipped - 7 lines] > > Paul The issue that can stop you from being able to do this (which could be answered IF you posted the queries) is if the criteria for each of the queries is different. If the criteria is different then by combining the query criteria you end up typically stating that ALL of the criteria has to happen on the record at the same time for the update to post. Ron Sorry about the lack of information: UPDATE [tbl CWT weekly] LEFT JOIN [CWT suspensions] ON [tbl CWT weekly]. Reason = [CWT suspensions].[Suspension reasons] SET [tbl CWT weekly].Reason = [CWT suspensions.Suspension code]; Thats the SQL statement and its the same for each of the four queries except instead of 'reason' the other fields are called field 33 field 36 and field 39. >The issue that can stop you from being able to do this (which could be >answered IF you posted the queries) is if the criteria for each of the [quoted text clipped - 5 lines] > >Ron That's a start. Post the other queries. AND post the query that does not work for you. By the way the set line of your posted query doesn't read correctly. The brackets around [CWT suspensions.Suspension code] should be [CWT suspensions].[Suspension code] UPDATE [tbl CWT weekly] LEFT JOIN [CWT suspensions] ON [tbl CWT weekly].Reason = [CWT suspensions].[Suspension reasons] SET [tbl CWT weekly].Reason =[CWT suspensions].[Suspension code] John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > Sorry about the lack of information: > [quoted text clipped - 15 lines] >> >> Ron UPDATE [tbl CWT weekly] INNER JOIN [CWT suspensions] ON [tbl CWT weekly]. Field39 = [CWT suspensions].[Suspension reasons] SET [tbl CWT weekly].Field39 = [CWT suspensions.Suspension code]; Above is another sql statement for another update query, it is identical to the previous sql statement posting besides the column name is field 39, both work fine alone. when i put them in the same update query it looks like this: UPDATE [tbl CWT weekly] INNER JOIN [CWT suspensions] ON [tbl CWT weekly]. Field39 = [CWT suspensions].[Suspension reasons] SET [tbl CWT weekly].Field39 = [CWT suspensions.Suspension code], [tbl CWT weekly].Reason = [CWT suspensions.Suspension code]; but doesn't work the same as when both were in individual queries. Thanks for helping >That's a start. >Post the other queries. AND post the query that does not work for you. [quoted text clipped - 19 lines] >>> >>> Ron UPDATE [tbl CWT weekly] LEFT JOIN [CWT suspensions] ON [tbl CWT weekly].Reason = [CWT suspensions].[Suspension reasons] SET [tbl CWT weekly].Reason = [CWT suspensions].[Suspension code]; UPDATE [tbl CWT weekly] INNER JOIN [CWT suspensions] ON [tbl CWT weekly].Field39 = [CWT suspensions].[Suspension reasons] SET [tbl CWT weekly].Field39 = [CWT suspensions].[Suspension code]; You have changed the type of join and the join criteria between those two queries. You are going to get different results and there is really no good way to change all four fields at one time - at least based on my current knowledge of your table structures. John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > Sorry about the lack of information: > [quoted text clipped - 15 lines] >> >> Ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.