update query

[oldLearner57]

hi community

how can i do an multiples update query to a table if my updating is in
single column.

example:

I have a Field name: Position

and I need to update the "Manager" to "Senior Manager" && "Secretary" to
"Senior Secretary"

i can do a single update but if I wish to update both at same time how am I
to go about it?

thanks community for the help

 

[Tim M]

Try this,

UPDATE EMPLOYEE SET Position = 'Senior ' + Position
FROM EMPLOYEE
WHERE Position in ('Manager', 'Secretary')

 

[Tim M]

Sorry, I'm thinking SQL Server.

Should be

UPDATE EMPLOYEE SET Position = 'Senior ' & Position
FROM EMPLOYEE
WHERE Position = 'Manager' OR Position = 'Secretary'

 

[oldLearner57]

hi Tim M

thanks for you replied with the sql syntax (my sql knowledge is very basic)
on the mutliples updating,

besides using the sql syntax, how can I do the updating via the update query
dialog, I am using Access 2003 n in the update query dialog box, there is
only 1 fill box for me to enter 1 update data?

or is it I need to do few x to update the data if I use the Access Update
Query dialog box?

thanks n much appreciated in advance

&

thanks community as well

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the
way you expect.

 

[Pete D.]

Change the view to SQL, Paste in the SQL code, fix table name to your table
name and field names as needed. Then change view back to query grid to see
the results. Pete

 

[John Spencer]

Well. Strange. My reply got truncated.

Using the query grid
++ Add your table to the query
++ Add the position field to the query
++ Select Query: Update from the menu
++ Enter the following in the criteria block under Position
IN ("Secretary","Manager")
++ Enter the following in the UPDATE TO block
"Senior " & [Position]

Run the query.

In the SQL view that would look like

UPDATE [Your Table]
SET [Your Table].[Position] = "Senior " & [Position]
WHERE [Position] in ("Secretary","Manager")

Be aware that unless you have other criteria this will update every
record where the Position field is equal to Secretary or Manager.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

hi community

how can i do an multiples update query to a table if my updating is in
single column.

example:

I have a Field name: Position

and I need to update the "Manager" to "Senior Manager" && "Secretary" to
"Senior Secretary"

i can do a single update but if I wish to update both at same time how am I
to go about it?

thanks community for the help

As an alternative to Tim's suggestion - which is a good one since the update
pattern is the same for both fields - you can use the Switch() function. In
the query grid Update To cell under Position you can put:

Switch([Position] = "Manager", "Senior Manager", [Position] = "Secretary",
"Senior Secretary", True, [Position])

Also put a criterion in the Criteria box under the same field:

IN ("Manager", "Secretary")