Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2006
    Posts
    12

    Unanswered: Update query for multiple values in one field

    Hi all!
    I am trying to create an update query that will allow me to replace color
    names within two fields in a table.
    I have a table Products with two fields: BGColor and BRColor.
    I was able to create an update query that does only one at the time.. This
    is the code i used:
    (here Navy will be replaced by Blue and Lime with Green, but there are a lot
    more colors that need to be replaced)

    UPDATE 456 SET Background = "Blue"
    WHERE Background="Navy";

    Now I have a big list of colors that need to be changed, so I tried to use
    the code:
    UPDATE 456 SET Background = "Blue"
    WHERE Background="Navy";
    UPDATE 456 SET Background = "Lime"
    WHERE Background="Green";

    But I get an error on this on this...
    Any ideas what I am doing wrong or even better, how to accomplish this?
    Please be gentle, since I'm a newbie to all of this ;-)

    To give you an example:
    BGColor BRColor - Must become - BGColor BRColor
    Navy Red Blue Red
    Yellow Lime Yellow Green
    Lime Navy Green Blue

  2. #2
    Join Date
    Oct 2006
    Posts
    11
    Create a new table which will serve as a mapping table with two columns. The first column containing the old color and the second column containing the new value.

    Create a query that links the fields that you want to update with the 'old' color value, and update the field with your 'new' color value.

  3. #3
    Join Date
    Dec 2006
    Posts
    12

    Smile Worked out great!

    Thanks for the advice!
    This indeed worked out prefect

  4. #4
    Join Date
    Oct 2006
    Posts
    11
    You're welcome!

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Out of curiousity, this isn't for a web-based platform is it? I'm glad your issue was solved, but I wonder if you couldn't be better served using other methods...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Dec 2006
    Posts
    12

    Actally it is...

    It indeed is for a web based platform, but since I'm no programmer I thought this would be my best solution.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ah, have you considered using CSS?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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