joins

[robertd]

I have three tables;
Advisor, Referral and Action.
Advisor is linked to Referral in a one to many relationship. Advisor being the one.
Referral is linked to Action in a one to many relationship. Referral being the one.
If I run a query to find all the referrals and actions therefrom I only get the Referrals where there is data entered in the Action table.
How do I overcome this please?
Rob

[tstutting]

Ok, this will be long since I'm not exactly seeing what you want to do so I'll use my own example.

Table Fruits (Fs) with field Fruit(F) contains Oranges, Apples Grapes and Tomatos
Table Vegies(Vs) with field Vegie(V) contains Potatos, Carrots, Grapes and Tomatos

If I want only what is common between Fs and Vs, I make a query with Table Fs and Table Vs joined F - V using option 1 (double click the relationship line) INNER JOIN or equal join. Returns Grapes and Tomatos

If I want only Oranges and Apples (in Fruits but not in Vegies) I bring in Table Fs and Vs joining F --> V using option 2 (Left Join) and typing NOT NULL in the criteria of V

If I want only Potatos and Carrots, I bring in Table Fs and Vs joing F to V using option 3 (right join) and typing NOT NULL under F

If I want Grapes Tomatos Oranges and Apples I do the Left Join but without the NOT NULL

If I want Oranges Apples Potatos and Carrots, but not Grapes or Tomatos, it's more complicated and you have to use the SQL view of the query. The code is this
SELECT Fruits.Fruit, Vegies.Vegie
FROM Fruits LEFT JOIN Vegies ON Fruits.Fruit = Vegies.Vegie
WHERE (((Vegies.Vegie ) Is Null))
UNION ALL
SELECT Fruits.Fruit, Vegies.Vegie
FROM Fruits RIGHT JOIN Vegies ON Fruits.Fruit = Vegies.Vegie
WHERE Fruits.Fruit is null;

The last Option would be a FULL OUTER JOIN that would return Grapes, Tomatos, Oranges, Apples, Potatos, and Carrots and again you need to use SQL only but is the same code with an INNER JOIN as follows:

Select Fruits.Fruit, Vegies.Vegie
From Fruits INNER JOIN Vegies ON Fruits.Fruit = Vegies.Vegie
UNION ALL
SELECT Fruits.Fruit, Vegies.Vegie
FROM Fruits LEFT JOIN Vegies ON Fruits.Fruit = Vegies.Vegie
WHERE (((Vegies.Vegie ) Is Null))
UNION ALL
SELECT Fruits.Fruit, Vegies.Vegie
FROM Fruits RIGHT JOIN Vegies ON Fruits.Fruit = Vegies.Vegie
WHERE Fruits.Fruit is null;

Hope this helps
-Trevor