Access Left Outer Join - Design View

Posted on 2009-04-25
Last Modified: 2012-05-06
I have Tables A and B. I want to only keep in Table C, the records from Table A that are not at all in Table B. In a Venn diagram, this would be the left most half moon of Table A. The entire circle of Table B would not be in Table C.

Is there a manner to create this in Query Design view, without using SQL code? All join options in Design View include at least partial inclusion of records from Table B.

Question by:tahirih
    LVL 92

    Accepted Solution

    You can do this wholly in design view.

    1) Add TableA and TableB

    2) Use drag and drop to change the INNER JOIN to LEFT JOIN/RIGHT JOIN by double clicking the
    "link line" between TableA and TableB and editing the join options

    3) Add at least one column from TableB to the query, uncheck the "display" checkbox, and enter Is Null in
    the criteria for that column

    Author Comment

    Let me also add tht this is a modified Left Outer Join, since I am extracting out records from Table A that are in Table B.

    LVL 92

    Assisted Solution

    by:Patrick Matthews

    Yes, and I crafted the instructions above with that in mind.

    Perhaps it would be easier if you entered a query like this in SQL view, then switched to design view to see
    what Access does with it:

    SELECT a.*
          TableB b ON a.ID = b.ID
    WHERE b.ID Is Null



    Author Closing Comment


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article describes some very basic things about SQL Server filegroups.
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now