• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

Show all Records on left side

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
pdvsa
Asked:
pdvsa
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You need to use a LEFT JOIN ... example:

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


mx
0
 
pdvsaAuthor Commented:
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
 
Aaron TomoskyTechnology ConsultantCommented:
Left right outer And inner are the 4 types of joins.  All handy in different situations.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
see this
join.jpg
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Another image to go with first image ...

mx
Capture1.gif
0
 
pdvsaAuthor Commented:
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

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!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now