dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 9 of 9

Thread: Outer Join in MS Access

  1. #1
    Join Date
    Jan 2003
    Posts
    30

    Question Outer Join in MS Access

    Hello guys,

    I need help with MS Access. Just wondering if there actually exists an OUTER JOIN option in MS Access? If so how do I access it? If I m not allowed to use OUTER JOIN in MS Access, what is the way around it? Haven't had any luck so far.

    Please help!

  2. #2
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: Outer Join in MS Access

    You can use both Left as well Right outer Joins in MS-Access.

    This query will diaplay all records afrom Table1 and only matching records from Table 2
    PHP Code:
    SELECT Table1.*, Table2.* FROM Table1 LEFT JOIN Table2 ON Table1.ID Table2.ID
    And this query will display all records from Table2 and only matching records from Table1
    PHP Code:
    SELECT Table1.*, Table2.* FROM Table1 RIGHT JOIN Table2 ON Table1.ID Table2.ID

  3. #3
    Join Date
    Jan 2003
    Posts
    30

    Re: Outer Join in MS Access

    Thanx Shuja Ali

    I have 3 tables which I am quering and can't seem to get the join thing right. My tables are claim,addressbook and person, I need information from claim and person irregardless of whether addressbook has maching records.

    This is how my select statement looks like:

    SELECT c.linkno,c.agent,c.serialno,c.branch,c.subfileno,c.registeredon,(trim(p.surname) & ' ' & trim(p.initials)) AS claimant FROM (addressbook AS a RIGHT JOIN claim AS c ON a.referenceno = c.attorney) INNER JOIN [person] AS p ON c.linkno = p.linkno WHERE p.capacity = 'C'

    This statement returns no records in VB, but if I query the statement in MS Access I get some records back.

    How do I flag out null values co that I get some records back?

  4. #4
    Join Date
    Jan 2003
    Posts
    30

    Re: Outer Join in MS Access

    I have managed to sort out my join problem.

  5. #5
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: Outer Join in MS Access

    Quote Originally Posted by HappyGirl
    I have managed to sort out my join problem.
    That is how it should be

  6. #6
    Join Date
    Feb 2008
    Posts
    1

    Re: Outer Join in MS Access

    Happygirl, Please can you share your code with me? I'm stuck with the same problem... Thanks!!

  7. #7
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: Outer Join in MS Access

    Quote Originally Posted by mrexite
    Happygirl, Please can you share your code with me? I'm stuck with the same problem... Thanks!!
    Do you understand that this thread is pretty old and HappyGirl has not logged on to Code guru since October 4th,2006. It is always better to ask a question rather than revoking an old thread and that a thread that is one and half year old.

  8. #8
    Join Date
    Apr 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    1,792

    Re: Outer Join in MS Access

    At least he searched the forum(s) before asking the question

    I like people who do that - they're obviously trying to help themselves BEFORE they ask the rest of us
    Be nice to Harley riders...

  9. #9
    Join Date
    Feb 2002
    Location
    Makati City, Philippines
    Posts
    1,054

    Re: Outer Join in MS Access

    Quote Originally Posted by Twodogs
    At least he searched the forum(s) before asking the question

    I like people who do that - they're obviously trying to help themselves BEFORE they ask the rest of us
    Agree. And probably out of excitement of possibly finding the solution, mrexite failed to notice the date. I ran into the same experience in the past.
    Marketing our skills - please participate in the survey and share your insights
    -

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)