Results 1 to 3 of 3
  1. #1
    elenaluiza is offline Novice
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    1

    Join fields in 2 tables and adding a criteria

    Hello,



    I have an issue with 2 join tables.
    I have 2 product tables with product codes and product names. Table A and Table B.

    T B and T A have in common column ID product code, a numeric attribute. I want to find out which products from Table A can be found in Table B, and there where the joint fields are equal I want to add (in the resulted query) a column (lets name it Results)to mark the equal joint fields with the name Product1, and where the joint fields are not equal ( products from B which are not in A)to mark them in the same column Results with the name Product2 or null. In fact I want a table with all codes, and where codes from Table A can be found in Table B to be marked with a value and where are not to be marked with an other value.

    Can this query be made in one stept or more? And which are the steps?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    In your tables and while in design view, make sure that your ID field's Indexed property = Yes. When you are in design view of your query, double click the line that represents the JOIN and adjust the JOIN properties so, "Include all records from 'T1' and only those ...".

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    A join (is a line) will result in only records that are found in both tables.

    A left join, described already by IM, (is a line with an arrow) will result in all the records of the left table and the table point to will have nulls if it does not have corresponding data

    That's the join. Once you have the correct join, then you drag down all the fields you need and then also create new 'calculated fields' that always begin with an invented name. For instance
    Both:

    in this case you might use the Iif method to detect 'both' i.e.:
    Both: iif(Table1.Product1=Table2.product1, "Yes", "No")

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding fields to template tables/forms
    By mkfloque in forum Access
    Replies: 1
    Last Post: 07-14-2014, 09:57 AM
  2. Replies: 1
    Last Post: 03-07-2014, 11:37 AM
  3. Replies: 6
    Last Post: 02-11-2014, 05:20 AM
  4. Replies: 1
    Last Post: 01-28-2013, 11:59 AM
  5. Replies: 3
    Last Post: 08-06-2009, 11:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums