Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Unanswered: need to compare two tables and update one field

    Sorry but I'm an Access VBA Noob... I have 2 tables, one with a set of number codes with descritions of the codes, like a lookup field i guess.
    The other table has a unique id number and 3 other columns, column A,B,C. These columns have the codes from the first table in them. But each column over will override the one before it if the codes are different.

    So basically what i want to do is go through the second table, open column C of codes(4th column in the table and most hierarchial) check to make sure it is valid checking it against table 1, if it is I would like to put it into a new 4th code Column D (final code). If that isnt a valid code or is empty I would like to go to the less powerful column B, check it against table 1 codes and apply it to column D if it is valid. Samething if it isnt valid, just using column a instead of b... I'm using access 97. Thanks for any help.


    If column C is valid Then
    column D = column C
    Else
    If column B is valid Then
    column D = column B
    Else
    If column A is valid Then
    column D = column A
    End If
    End If
    End If

  2. #2
    Join Date
    Jul 2003
    Location
    Kaukapakapa, NZ
    Posts
    15

    Re: need to compare two tables and update one field

    merce,

    You could use standard queries as follows:

    1: Select query on both tables linking the number code to column C. This will show only records that match.

    2: Change this to an update query and add column D.
    Set the update to for column D to [column C]

    3:A new select query linking number code to column B but include column D with criteria is null.
    This will show only records that have not been updated by step 2 and match.

    4: Change to an update query.
    Set update to for column D to [column B]

    5:A new select query linking number code to column A but include column D with criteria is null.
    This will show only records that have not been updated by step 2 & 3 and match.

    6: Change to an update query.
    Set update to for column D to [column A]

    If you are comfortable with Access you can miss the select query part and just create the update queries.

    If you need to do this frequently you could then create a macro with query1 query2 query3 and then convert this to code to see the VBA used.

    Hope this helps.

Posting Permissions

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