1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: (MS Access 2007) Linking 2 Tables

Discussion in 'Business Applications' started by blue77, Sep 17, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. blue77

    blue77 Thread Starter

    Joined:
    Aug 15, 2012
    Messages:
    32
    Hello, I am using Windows 7 and MS Access 2007. I have 2 tables. Both tables have the "NAME" field. I also have the "OK" field with data type of "Yes/No" in the second table. These two tables have different records of NAME. But sometimes, these NAMEs match each other. What I want to ask is, how can I link these two tables so when there are records with the field of "NAME" that match in two tables, the field of "OK" will be filled with value "Yes". Otherwise, the value is "No".

    Any help would be appreciated. Thanks.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    You would use 1 or 2 queries to do this, the first query to match the names and the second to "Update" the "OK" field, it may be possible to do it with just one "Update" query.where the names are matched.
     
  3. Mhouser

    Mhouser

    Joined:
    Jan 21, 2008
    Messages:
    138
    would something like this work for you?

    UPDATE TABLENAME
    SET OK=Yes
    WHERE NAME IN (SELECT NAME FROM OTHERTABLE)

    UPDATE TABLENAME
    SET OK=No
    WHERE NAME NOT IN (SELECT NAME FROM OTHERTABLE)
     
  4. blue77

    blue77 Thread Starter

    Joined:
    Aug 15, 2012
    Messages:
    32
    I create 1 query consists of NAME from TABLE1, NAME from TABLE2, and OK from TABLE2. Here is how I fill the query:
    1. Fill the Criteria under the column of NAME from TABLE2 with: "=[TABLE1].[NAME]"
    2. Fill the Update To under the column of OK with: "Yes"

    Try running the query and it shows only the Field of OK with NO from top to the bottom even though there are several records in two tables that match each other in term of NAME.

    Oh, and by the way, is this method worked if I the NAME is not in the same record but have the same value? For example, there is "Bryan" in the first record from TABLE1 and there is another "Bryan" in the eleventh record from TABLE2.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Can you post a dummy database?
     
  6. blue77

    blue77 Thread Starter

    Joined:
    Aug 15, 2012
    Messages:
    32
    I have attached the dummy database.
    Oh, in the query, I just put both table inside and have not done any update because whenever I make the update and then close the database, the query is not open-able saying that it is in Disable Mode.
     

    Attached Files:

  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, the query now shows 2 common names between the 2 tables, Bryan & Charlie.
    It updates the OK field for those 2 to Ticked, the rest stay Unticked.
     

    Attached Files:

  8. blue77

    blue77 Thread Starter

    Joined:
    Aug 15, 2012
    Messages:
    32
    Woah!!! I don't know that it would be this easy.
    Thank you very much, OBP.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1069249