update query

[Clemens]

It seems no one can correctly help me with my problem. So I try it again and
now in this topic.

I have a table with muliple fields (columns).
In one of these columns all rows can contain only 3 different values: for
example 'aaa' or 'bbb' or 'ccc'.

I need a correct update query where I can change it for example to the
following:

column: number test --> update to -->
1 aaa --> xxx
2 bbb --> yyy
3 ccc --> zzz
4 aaa --> xxx
5 aaa --> xxx
6 ccc --> zzz
7 bbb --> yyy
8 ccc --> zzz

I'm able to create an update query for only 1 value, but can't seem to
figure out how to update multiple values at once.

Please help

 

[Clemens]

I have forgot to add some information:

I have created a switchboard and the update query needs to be started from
the switchboard. I can't see the option to start a created query from the
switchboard only run code or macro. So the only option is to start the query
using a code or macro. But first things first.....I have to start from the
beginning

 

[Clemens]

I have done following: created 3 seperate update query's. Create a macro to
run these 3 update query's. The is a way, but I'm not sure if this is the
best way.

When I start the macro I recieve a warning at each update query which I need
to click yes. Can this message be bypassed?

 

[Jeanette Cunningham]

Clemens,
to turn warnings off in A2000 - A2003
Tools | Options | Edit/Find | Action queries -- uncheck the box next to
action queries.
This turns off warnings for all action queries permanently until you change
the setting by checking the box.

Note: The layout of your columns and data does not look normalized.
Normalize your data to best take advantage of access' relational features.

Jeanette Cunningham

 

[John Spencer]

The method I would use would be to add a new table
ConversionTable
OldValue
NewValue

Add a record for each pair of values

Now use an update query that looks like

UPDATE [YourTable] as C Inner JOIN ConversionTable
ON C.Test = [ConversionTable].[OldValue]
Set C.Test = ConversionTable.NewValue

To build this query with the grid
-- Open a new query
-- Select your table and the Conversion table
-- Drag from your field to the OldValue field on the Conversion table to set
up the join
-- Select the field from your table that you want to change
-- Select Query: Update from the menu
-- Type [ConversionTable].[OldValue] into the Update To: "cell"
-- Save the query as "MyConversionQuery"

Build a macro to execute the query. OR better build a vba routine to run
the query.

The absolutely simplest code I can think of to do this is as follows. You
should be able to put this into a VBA module and save the module (with a
name other than "RunMyQuery"

Public Sub runMyquery()
CurrentDb().Execute "MyConversionQuery", dbFailOnError
End Sub

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Clemens]

Hi Jeanette,
Thanks for answering. It worked

Note: The layout of your columns and data does not look normalized.
Normalize your data to best take advantage of access' relational features.

I don't understand, can you please explain this further. I'm in a learning
proces for access