Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    1

    Unanswered: Compare two fields and update a third field

    I'm new to VBA, intermediate SQL. I've been scouring the web for an answer - and can't find anything similar... or perhaps I'm searching for the wrong thing! I'm using Access 2007.

    I want to compare two columns from two different tables and then update another column.

    Table A is an audit trail.
    ID--CHANGETYPE--OBJECTID---BEFORE--AFTER--VALIDATED?
    1---ColorChange--Object1-----Red------Blue----No

    Table B is the current status of the object:
    OBJECTID---COLOR
    Object1-----Blue

    So using the sample data above: Table A says Object1 should be Blue now because it had a color change. Table B says Object1 is currently Blue.

    So I would like to create some vb code that goes something like this:
    For each row...
    If tableA.ChangeType = "ColorChange" then
    if tableA.After = tableB.Color then
    set tableA.Validated to "Yes"

    Thanks in advance for the assistance!!!
    Last edited by dknj30; 04-23-11 at 22:43. Reason: ease of reading

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You can do it in SQL:
    Code:
    UPDATE Table_A INNER JOIN Table_B ON 
           Table_A.AFTER = Table_B.COLOR 
       SET Table_A.VALIDATED = True
    WHERE (Table_A.CHANGETYPE="ColorChange");
    Have a nice day!

Tags for this Thread

Posting Permissions

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