Show record if no match in Query

Posted on 2011-10-03
Last Modified: 2012-08-14
I have multiple tables that I am querying to combine a list of all data for a project. I realized that i am missing records if one of the tables does not have any information listed for the project. I would still like to show all the other data from the other tables and leave the field that had no value blank.
Any thoughts.
Attached is the Database. I am working on the "Project Query". The tables that may not have any details for a project at a specific point in time are the "Oracle Extract" and  "Expense"    
Question by:SMP319
    LVL 14

    Expert Comment

    You can use the Query Wizard in Access to answer this common problem.  Choose Create->Query Wizard and then choose Find Unmatched Query Wizard.

    Click OK and then choose Table: Project, Next.
    Choose Table: Oracle Extract, Next
    Choose the fields that should have matching data. It looks like you want PAS Code from Project and Project from Oracle Extract, but you'll know best. The fields must have the same data type.
    Choose which fields from Projects you want to show. Next.
    Refine the name of the query, if desired. Finish.

    The new query opens with no records. That means every PAS Code in Projects has at least one matching record Project in Oracle Extract.

    If you repeat with Expenses, you'll see several projects do not have matches in Expenses at this point.

    LVL 26

    Expert Comment

    <I realized that i am missing records if one of the tables does not have any information listed for the project>
    That sounds like you need a LEFT JOIN on that table instead of an INNER JOIN.
    I cannot open accdb format, so I can't look and see.
    LVL 1

    Author Comment

    PT 72 I am looking to have the record from Projects show in the query even though there is no matching record in the oracle extract file.  The Query is pulling the 2 tables together. Your solution seems to just identify the un matched records
    LVL 14

    Accepted Solution

    If you want both the Projects records with Oracle Extracts and those without, the Nick's suggestion of a LEFT JOIN query is what you want:

    Create a query. Add Projects and Oracle Extracts. Drag the matching field (PAS Code) from Projects on top of Project on Oracle Extract. When you let go, a INNER JOIN is created by default. Double-click the connecting line and choose Show all records from 'Project' and only those records from 'Oracle Extract' where the joined fields are equal.

    The result will be an arrow pointing the Oracle Extract side. Add the fields you want from both tables. Your result will have the same number of records as Project and the included fields for OE on matching records.

    LVL 44

    Expert Comment

    by:Vitor Montalvão
    I don't have Access 2007 installed in my computer. Can you post here the query?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    794 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

    18 Experts available now in Live!

    Get 1:1 Help Now