update 4 columns in one query?

[Accessghostrider via AccessMonster.com]

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

 

[John Spencer]

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

 

[Ron2006]

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

 

[Accessghostrider via AccessMonster.com]

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.

 

[John Spencer]

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:

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
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

 

[Accessghostrider via AccessMonster.com]

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.

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]

 

[John Spencer]

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:

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
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