Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Show record if no match in Query

Posted on 2011-10-03
Medium Priority
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

ID: 36906208
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

ID: 36906705
<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.

Author Comment

ID: 36911624
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

pteranodon72 earned 2000 total points
ID: 36912872
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 53

Expert Comment

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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

572 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