Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: Comparing 2 tables and updating a Yes/No field.

    Real new to Access and not even real sure how to ask this.

    I have 2 tables that I need to compare data in. Table #1 is a master list and table #2 is a secondary list of items. I want to run a query that will basically look at the data in both tables and if it is present in both update a column in the master list that is set to Yes/No data.

    Is this possible?

    Any help is much appreciated.

  2. #2
    Join Date
    Nov 2004
    Posts
    3
    This is an example of what I want.

    I want to compare table2 against table1, and if they are in both I want to check the appropriate check box in field3 in table1.
    Attached Files Attached Files

  3. #3
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Here you go. I added an update query called qryMatched_Yes thay updates the Field3 value to Yes if the value exists in both tables.

    TD
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    Hi TD. I just took a look at your example and was wondering how the query makes the determination that the record exists in field 1 of both tables? I understand how the update query writes to the table, but where is the actual critera for the query? All I see is:

    Field: Field3
    Table: Table1
    Update To: Yes

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by smacdonaldnc
    Hi TD. I just took a look at your example and was wondering how the query makes the determination that the record exists in field 1 of both tables? I understand how the update query writes to the table, but where is the actual critera for the query? All I see is:

    Field: Field3
    Table: Table1
    Update To: Yes
    HI smacdonaldnc,

    When you see the Query, look at the line between the two fields in both tables. That is what tells it to Match one against the other. And if it does then simply Update Field3 in Table1 to Yes. The line between the two does the trick. It might help if when you're looking at the QBE view of the Query to go to the MenuBar and Click on View, and the SQL view. It kinda explains itself better that way.
    Code:
    
    UPDATE Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 SET Table1.Field3 = Yes;
    
    See....Update Table1 AFTER you create an InnerJoin with Table2 IF Table1.Field1 = Table2.Field1 The SET is what checks the box in Table1.Field3

    that help you out any?
    have a nice one,
    BUD

  6. #6
    Join Date
    Nov 2004
    Posts
    3
    Quote Originally Posted by buckeye_td
    Here you go. I added an update query called qryMatched_Yes thay updates the Field3 value to Yes if the value exists in both tables.

    TD
    It is official, you are my hero for today...that is exactly what I was looking for. After looking at the query it is a lot easier than I was trying to make it.
    Thanks so much for your help.

Posting Permissions

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