Show record if no match in Query

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"    
Account-Project-Management.accdb
LVL 1
SMP319Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pteranodon72Commented:
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.
Next.
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.


HTH
pT72
0
Nick67Commented:
<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.
0
SMP319Author Commented:
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
0
pteranodon72Commented:
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.

HTH,
pT72
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't have Access 2007 installed in my computer. Can you post here the query?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.