Solved

Show all Records on left side

Posted on 2011-03-05
8
452 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
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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

932 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