Outer Join in MS Access

[HappyGirl]

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!

[Shuja Ali]

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

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

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

[HappyGirl]

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?

[HappyGirl]

I have managed to sort out my join problem.

[Shuja Ali]

I have managed to sort out my join problem.

That is how it should be

[mrexite]

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

[Shuja Ali]

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.

[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

[aio]

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.