Add a Field (XP)

Topic

New Reply

I have a table (TableA) with various fields in it (say FieldA, FieldB and FieldC). I need to add another field (FieldD) whose value depends on the values from the other three fields.

Field D = 1 if FieldA = 1
Field D = 2 if FieldA = 2 and FieldB = 2
Field D = 3 if FieldA = 2 and FieldB = 4
Field D = 0 in all other cases.

Here is where my knowledge of Access shows (or doesn’t show is more accurate). To do this, I would (in this order) …

a) change the structure of TableA to include a new field (FieldD)
write an update query “UPDATE TableA SET FieldD = 0”
c) write an update query “UPDATE TableA SET FieldD = 1 WHERE FieldA=1”
d) write an update query “UPDATE TableA SET FieldD = 2 WHERE FieldA=2 AND FieldB=2”
e) write an update query “UPDATE TableA SET FieldD = 3 WHERE FieldA=2 AND FieldB=4”

I’m hoping that there is an easier way – maybe combining the 4 queries down to 1?

Reply | Quote

Replies

Assuming that the field types for a table with fields AA, BB, CC, and DD are numbers and you are updating the data in a query and not code,

Create an update query and input the following in the Update To field for Field DD.

IIf([AA]=1,1,(IIf([AA]=2 And [BB]=2,2,(IIf([AA]=2 And [BB]=4,3,0)))))

HTH

Post immediately edited to fix formatting problem.

Reply | Quote

Thx Gary / Hans,

I knew I was missing something! Put the if statements inside the ‘set to what part’ instead of being in the ‘select what’ part. Perfect.

Also, the reason I am actually going to the bother of creating this extra field is that I am just using the dbase as a datasource for a pivot table in excel.

Reply | Quote

You can base a pivot table in Excel on a select query in Access, so unless performance becomes an issue, there is no need to add the 4th field to the table.

Reply | Quote

Gary already posted an expression.

Strictly speaking, you don’t need Field D in the table, since it contains derived information. You can calculate it in a Select query, and use the query as basis for further queries, forms and reports. You’d use a calculated colum with an expression similar to that posted by Gary.

Reply | Quote