Solved

Show all Records on left side

Posted on 2011-03-05
8
473 Views
Last Modified: 2012-05-11
Experts,
In the following qry, I need to show all records on the left side.  Many projects do not have an associated record in the tblLetterOfCredit based on ProjectID.  I need help to show ALL records on tblProjects even if there is not a match on tblLetterOfCredit.  
How can I do this?   Please see attachment
 qrydesign
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 400 total points
ID: 35044702
You need to use a LEFT JOIN ... example:

SELECT Table1.*, Table2.D1
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.D1;


mx
0
 

Author Comment

by:pdvsa
ID: 35044731
OH sweet!  that worked.  Is the only way to do a LEFT JOIN is to actually go into the SQL?  I thought I could change the JOIN type by changing the directions of the arrows.

thanks!
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35044744
Left right outer And inner are the 4 types of joins.  All handy in different situations.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75
ID: 35044797
"I thought I could change the JOIN type by changing the directions of the arrows."
Sure ... you can.  That SQL was in fact the result of doing just that - see image.

mx
Capture1.gif
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 100 total points
ID: 35044800
Is the only way to do a LEFT JOIN is to actually go into the SQL?

NO

place two tables in the design view of a query
create a join on two fields by dragging the field from the left table to the field on the other table

now, right click on the line that joined the two fields

a window will open with three choices, select the second option
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35044809
see this
join.jpg
0
 
LVL 75
ID: 35044812
Another image to go with first image ...

mx
Capture1.gif
0
 

Author Comment

by:pdvsa
ID: 35044928
Yes, I did know of right clicking those lines between the tables and choosing the differnt options but I did this in the qry design and it seemed to not include the LEFT side join.  Anyways, I am good now.  I must have been doing something wrong.  

I see now from those pics above, that I never really paid too much attn too, that there is a name for LEFT table and RIGHT table.  

thanks for the good info.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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