access query design help - adding field while keeping all records

I have 2 tables with a common field (Table A and Table B). There is another field on Table B with 'yes' as the cell data in each record. I want to query the tables to match the common field on Table B to Table A with the end result being Table A having a new field with 'yes' as the record data where the two common fields matched, and having a blank or null entry where there was no match.

If this were excel I'd just run a vlookup. I'm trying to do something similar in access.

Thx for any help.

Originally Posted by cytop

You cannot modify the structure of a table with a query (Generalisation ignoring DDL), but you can write a query to create a new table with the structure you want.

If you need the original Table A modified and then updated, you can use VBA to modify the structure before running the query.

Thanks for the reply. I tried creating a new table off of a query, but the result would be limiting the table to all cases where the common fields are matching. I'm actually looking for a result (table) where the common fields do not match. Is this possible through a query and not VBA? I'm not saying it shouldn't be done with VBA, but I have no experience with the coding of VBA in access.

Thx.

Sorry. I'm confused:

where the two common fields matched

(Post #1)

where the common fields do not match

(Post #4)

Originally Posted by Arkadi

To make a new table out of it:

Select Table1.*, [Table2].[YesFieldName] INTO [Table2] FROM [Table1] LEFT JOIN [Table2] ON [Table1].[CommonFieldName] = [Table2].[CommonFieldName];

This worked. Wow, thanks for the help! Much appreciated. Now that you showed me the solution, I hope I can learn what's actually happening in this code!

Thx again to Arkadi and cytop for the help.

Originally Posted by cytop

Sorry. I'm confused:

(Post #1)


(Post #4)

Sorry for any confusion. I was trying to add a field into Table A from Table B, but when I was running the make table query the tables were only showing records that matched Table B. I was trying to keep all of Table A's records intact while adding a field from Table B. Ultimately I only will be using data where Table B's field does not match Table A's.

Again, thx for the suggestions and sorry about any confusion!

Originally Posted by Arkadi

LEFT JOIN takes all data from first table and only matching from second table, then any fields from the second table will be null where there was no match between the two. An INNER JOIN gives only results where both tables have a match. And an OUTER JOIN would return data present in either Table 1 or Table 2, with blanks for all non-common fields where there was no match.

Thx, Arkadi! The JOIN functions seem very useful for my needs. I appreciate the explanation!