Solved

Show all Records on left side

Posted on 2011-03-05
8
448 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) 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 38

Expert Comment

by:Aaron Tomosky
ID: 35044744
Left right outer And inner are the 4 types of joins.  All handy in different situations.
0
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now