Query - left join, rt join...

Posted on 2011-10-26
Last Modified: 2013-11-27

I have a form with various fields from tblLetterofCredit.
It is joined to tblBanks_Participating on tblLetterOfCredit.LetterOfCreditID = tblBanks_Participating.LCID;

Not all tblLetterOfCredit records will have a tblBanks_Participating on the key
The form will not display the record from tblLetterOfCredit if tblBanksParicipating does not have a record. Meaning that my record in tblLetterOfCredit will not be displayed.
I only have this issue if there is no matchiing record in tblBanks_Participating.
I need to show ALL records from tblLetterOfCredit and matches on tblBanks_Participating but if there is no match on tblBanks_Participating then still show that one on tblLetterOfCredit.

How can I avoid this?
I thought I could use a LEFT JOIN from tblLetterOfCredit and it will show all records even if there is not a match for tblBanks_participating.
I imagine there is some kind of trick to this.

Thank you.

Here is the SQL:
SELECT tblLetterOfCredit.ProjectID, tblLetterOfCredit.Amount, tblLetterOfCredit.LCNo, tblLetterOfCredit.LCType, tblLetterOfCredit.Currency, tblLetterOfCredit.InitialExpireDate, tblLetterOfCredit.DateOfIssueSB, tblLetterOfCredit.FinalMaturity, tblLetterOfCredit.EndUserID, tblLetterOfCredit.ValidUntil, tblLetterOfCredit.CSMNo, tblBanks_Participating.BankID, tblBanks_Participating.BankType

FROM tblLetterOfCredit LEFT JOIN tblBanks_Participating ON tblLetterOfCredit.LetterOfCreditID = tblBanks_Participating.LCID;
Question by:pdvsa
    LVL 61

    Accepted Solution

    <I thought I could use a LEFT JOIN from tblLetterOfCredit and it will show all records even if there is not a match for tblBanks_participating

    The LEFT JOIN in your query should make it behave as you have described.

    Is there something else at work here, such as Form Filtering?

    What is the actual recordsource of your form?  Is it this query, or is it something else that is based on this query?
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    left join will do that...
    but you need to make sure you don't have any other criteria on the left joined table ...

    Author Closing Comment

    I am thinking I had some kind of corruption because it is working now.  

    thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    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…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now