Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22

    Unanswered: Update query with criteria issue!

    Hi All,

    I wondered if anyone can help me.

    I have two separate tables which have many common fields. One such field is called [contact number].

    I want to update table1 with all of the information from table2 where the [contact number] field is not the same i.e. exclude data where there is a match.

    I have tried to do this, but am either: 1) too inexperienced (I'm not a developer, just a lowly marketing person), 2) too tired (it's been a long, rough day) or 3) too stupid (blame the parents... I'm a product of their joint gene pool!)

    Thanks a million.

    John

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So both tables are identicle in structure or what?
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22
    Yes - structure is the same. All field properties are identical.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    k... I assume once you do this, you will only use one table... right?


    INSERT INTO tbl1 (your, field, list, here)
    SELECT tbl2.your, tbl2.field, tbl2.list, tbl2.here
    FROM tbl2 LEFT JOIN tbl1 ON tbl2.[control number] = tbl1.[control number]
    WHERE tbl1.[control number] IS NULL;
    oh yeah... documentation... I have heard of that.

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

  5. #5
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22
    Absolutely. Single table is where I want to be!

    Will try your example now and let you know how I get on.

    J

  6. #6
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22
    Hi,

    I don't understand the reasoning behind the routine, but it worked so thank you very much for that.

    J

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have a look at right and left joins in the help text. They're quite helpful to understand down the road.
    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
  •