?
Solved

Show all Records on left side

Posted on 2011-03-05
8
Medium Priority
?
479 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 1600 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 400 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

777 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