Results 1 to 5 of 5
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add a Field (XP)

    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.

  4. #4
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •