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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

THINKINGWAY - 09 Nov 2007 18:26 GMT

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.

Reply to this Message

Dale Fye - 09 Nov 2007 19:15 GMT

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

Signature

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.
[quoted text clipped - 4 lines]
> Random Thoughts = Random Results
> Reality is how you think = thoughts create reality.

Reply to this Message

THINKINGWAY - 09 Nov 2007 19:22 GMT

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.
[quoted text clipped - 24 lines]
> > Random Thoughts = Random Results
> > Reality is how you think = thoughts create reality.

Reply to this Message

John Spencer - 09 Nov 2007 19:24 GMT

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

Signature

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

>I am trying to perform an update query to read fieldB and determine if it
>is
[quoted text clipped - 6 lines]
> Random Thoughts = Random Results
> Reality is how you think = thoughts create reality.

Reply to this Message

Marshall Barton - 09 Nov 2007 19:43 GMT

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

Signature

Marsh
MVP [MS Access]

Reply to this Message