Creating a new field based on the values of an existing field.

[Peter]

My aim is to "re-code" the contents of one field (V1) into another field v(2).

Example follows:

V1(existing field) V2 (new field)
2 B
2 B
3 C
4 D
4 D
1 A
3 C
etc.

There exists a “look up table†(clerical hard copy) to specify the
relationship between V1 values and V2 values. The solution to the problem
needs to incorporate this pre-specified relationship and be repeatable
whatever the value of V1.

I am using MS Access 2003 running in XP.

I would very much appreciate any assistance anyone can offer to start me
off. Please bear in mind I am only using the query grid, not the scripting
facility.

Many thanks in advance.

 

[scubadiver]

Expr1: IIF(V1=1,"A", IIF(V1=2,"B", IIF(V1=3,"C", IIF(V1=4,"D",""))))

That is a long method if you are using the whole alphabet. An alternative
idea would be to create a table with two columns. Eg:

NumberID (1-26)
LetterID (A-Z)

And create a relationship between the NumberID and V1. In the query the
numbers and letters will match up without having to do an IIF statement.

 

[John Spencer]

The best method I know is to use a translation table with the OldValue in
one field and the new value in a second field. You can use the translation
table to update the existing field in the table, fill a new field in the
table, or just link to it when you need the new value.

TranslationV1toV2 (one record for each unique value in V1)
fldOld
fldNew

You could specifically use something like the following query to fill the
fields in your existing table
UPDATE ExistingTable INNER JOIN TranslationV1ToV2
ON ExistingTable.V1 = TranslationV1ToV2.fldOld
SET ExistingTable.V2 = [TranslationV1ToV2].[fldNew]

I prefer linking to the translation table in my queries because
-- when the corresponding value (fldNew) changes, all I have to do is edit
the value in the table
-- when additional values (fldOld) are added, I only need to add another
record to the translation table
-- I don't need to run an update query whenever new data is added or
existing data is changed

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

 

[Peter]

thankyou scubadiver. i very much apprecitate your time. i will try your
suggestion.

 

[Peter]

thanks john, i will give your suggestion a try. i appreciate your help and
your time very much.
--
Peter

The best method I know is to use a translation table with the OldValue in
one field and the new value in a second field. You can use the translation
table to update the existing field in the table, fill a new field in the
table, or just link to it when you need the new value.

TranslationV1toV2 (one record for each unique value in V1)
fldOld
fldNew

You could specifically use something like the following query to fill the
fields in your existing table
UPDATE ExistingTable INNER JOIN TranslationV1ToV2
ON ExistingTable.V1 = TranslationV1ToV2.fldOld
SET ExistingTable.V2 = [TranslationV1ToV2].[fldNew]

I prefer linking to the translation table in my queries because
-- when the corresponding value (fldNew) changes, all I have to do is edit
the value in the table
-- when additional values (fldOld) are added, I only need to add another
record to the translation table
-- I don't need to run an update query whenever new data is added or
existing data is changed

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

My aim is to "re-code" the contents of one field (V1) into another field
v(2).

Example follows:

V1(existing field) V2 (new field)
2 B
2 B
3 C
4 D
4 D
1 A
3 C
etc.

There exists a "look up table" (clerical hard copy) to specify the
relationship between V1 values and V2 values. The solution to the problem
needs to incorporate this pre-specified relationship and be repeatable
whatever the value of V1.

I am using MS Access 2003 running in XP.

I would very much appreciate any assistance anyone can offer to start me
off. Please bear in mind I am only using the query grid, not the scripting
facility.

Many thanks in advance.