update queries

[STEVE]

we have a field that needs updated. what goes into that field depends on what
is in a different field. field 1 is the update field which could have various
answers. i can get it the correct answer when i have a one for one. where
field 2 eq "EG" field 1 updates to "X" but having problems with writing the
query when field 2 also as "TW" which i want field 1 updated to "P"

 

[KARL DEWEY]

Place this in your Update To: part of the query design grid --
IIF([field 2] = "EG", "X", IIF([field 2] = "TW", "P", [field 2]))

When there is not rationale to change then it does not.

 

[ken]

If field 1 *always* has a value of "X" if field 2 has a value of "EG",
and field 1 always has a value of "P" if field 2 has a value of "TW"
etc then your table contains redundancy, so field 1 is redundant and
should be deleted from the table. Instead you should have a separate
table with rows:

EG X
TW P
etc

If the value in field 1 are not always determined by that in field 2
then there is no redundancy (in the jargon field 1 is no longer
transitively functionally dependent). The separate table as above,
however is still the appropriate way to insert the values into field 1
as all that's needed is to join the tables on field 2 and then update
field 1 in your current table to the value of field 1 in the new
referenced table. The model here is analogous to having a UnitPrice
column in a Products table and a UnitPrice column in an InvoiceDetails
table; the former can change over time but the latter remain static as
the values from when the invoice was created. The former determines
the value for new invoices only.

Ken Sheridan
Stafford, England