Update of a field conditionally on another field in the same table

[THINKINGWAY]

I am trying to perform an update query to read fieldB and determine if it is
"Null" or blank, if this is the case then copy fieldA to fieldB's position.
Or the other way around, if fieldB has the letter "a" then copy fieldA to
fieldB's position.

Thank you,
Random Thoughts = Random Results
Reality is how you think = thoughts create reality.

 

[Dale Fye]

I don't recommend that. For one thing, in a normalized database, you would
never store the same piece of information twice in the same record.

What I would recommend instead, is using a query to capture that information
so that when you want info from your table (for any purpose), you write a
query. Your query might look like:

Select fieldA, IIF(LEN([FieldB] & "") = 0, [FieldA], [FieldB]), FieldC
FROM yourTable

If you really "must" update fieldB then the following Update query should do
it:

UPDATE yourTable
SET FieldB = FieldA
WHERE LEN(FieldB & "") = 0

HTH
Dale

 

[THINKINGWAY]

Excellent information...very helpful. Insight, since this is an action being
performed during an import process from an excel spreadsheet (occurs weekly)
I thought it woud be an appropriate approach.

I don't recommend that. For one thing, in a normalized database, you would
never store the same piece of information twice in the same record.

What I would recommend instead, is using a query to capture that information
so that when you want info from your table (for any purpose), you write a
query. Your query might look like:

Select fieldA, IIF(LEN([FieldB] & "") = 0, [FieldA], [FieldB]), FieldC
FROM yourTable

If you really "must" update fieldB then the following Update query should do
it:

UPDATE yourTable
SET FieldB = FieldA
WHERE LEN(FieldB & "") = 0

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

I am trying to perform an update query to read fieldB and determine if it is
"Null" or blank, if this is the case then copy fieldA to fieldB's position.
Or the other way around, if fieldB has the letter "a" then copy fieldA to
fieldB's position.

Thank you,
Random Thoughts = Random Results
Reality is how you think = thoughts create reality.

 

[John Spencer]

UPDATE YourTable
SET FieldA = NZ(FieldA,FieldB)
, FieldB = Nz(FieldB, FieldA)
WHERE FieldA is Null Or FieldB is Null



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

 

[Marshall Barton]

I am trying to perform an update query to read fieldB and determine if it is
"Null" or blank, if this is the case then copy fieldA to fieldB's position.
Or the other way around, if fieldB has the letter "a" then copy fieldA to
fieldB's position.

UPDATE table
SET FieldB = FieldA
WHERE FieldB is Null
OR FieldB = ""
OR FieldB = "a"