1. ## Add a Field (XP)

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)
[img]/forums/images/smilies/cool.gif[/img] 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?

2. ## Re: Add a Field (XP)

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.

3. ## Re: Add a Field (XP)

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.

4. ## Re: Add a Field (XP)

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.

5. ## Re: Add a Field (XP)

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•