Outer join not working

[kissybean via AccessMonster.com]

Hi All,

I have a problem doing an outer join between two tables. They are regular
access tables. I have read through all the questions and answers for the
topic and tried various things but still it will not work.

Table 1 has 205 records. I want to join with table 2 on an ID field. I have
3 criteria on table 2. I included the null clauses in various syntax's to no
avail.

Here is the query:

SELECT a.*, b.Rate
FROM [Provider Trend Rate Final] AS b RIGHT JOIN [Trend Projections - Step 1]
AS a ON b.Provider_ID = a.[Prov ID]
WHERE (((b.Product_Line)='COM') AND ((b.Type)='B') AND ((b.IP_OP)='IP')) OR (
((b.Product_Line) Is Null) AND ((b.Type) Is Null) AND ((b.IP_OP) Is Null));

This gets me 154 rows which is not correct. I want 205 rows.

Can anyone help?

Thanks!

 

[Michel Walsh]

A RIGHT JOIN preserve the table mentioned to the right of that word, here,
table "a", not table "b".

Vanderghast, Access MVP

 

[kissybean via AccessMonster.com]

A RIGHT JOIN preserve the table mentioned to the right of that word, here,
table "a", not table "b".

Vanderghast, Access MVP

Hi All,

[quoted text clipped - 24 lines]

Thanks!

Sorry, I meant that.... I have re-arranged this so many times... but the
point is the same... it doesn't work.

 

[Michel Walsh]

If you remove the WHERE clause, the number of returned records is ok?

Your WHERE clause handles the NULL coming from the join (if you use : FROM
a LEFT JOIN b ON ... WHERE b.f1=something OR b.f1 IS NULL), ***but*** still
REMOVES rows with a match that are such that b.f1=something. That is why you
may look at the result WITHOUT the where clause to see if the join is the
problem, or if it is not the where clause (which is logically executed after
the join).



Hoping it may help,
Vanderghast, Access MVP

A RIGHT JOIN preserve the table mentioned to the right of that word, here,
table "a", not table "b".

Vanderghast, Access MVP

Hi All,

[quoted text clipped - 24 lines]

Thanks!

Sorry, I meant that.... I have re-arranged this so many times... but the
point is the same... it doesn't work.