If a record does not exist in a table

[Emma]

Hi I have a complicated question. Right now I have the main table and the
case note table related to each other however in my query I would like to be
able to say pull all the clients from the main table which do not have a
record in the case note table. Is there a way to do this? Can you help me
please?

 

[Damon Heron]

SELECT *
FROM MainTable LEFT JOIN CaseNote ON MainTable.ID = CaseNote.ID
WHERE CaseNote.ID is Null;

Damon

 

[Emma]

Here's what I have but isn't working:
SELECT [Tbl Client Information].[Client ID]
FROM [Case Note Client] INNER JOIN [Tbl Client Information] ON [Case Note
Client].[Client ID] = [Tbl Client Information].[Client ID]
WHERE ((([Tbl Client Information].[Intake Coordinator])=["Staff Name"]) AND
(([Case Note Client].[Client ID]) Is Null));

 

[Jerry Whittle]

Sure thing. Create a query with both tables joined. Bring down all the needed
fields from the main table that you want to see. Next bring down one field
from the case table. In that field put Null in the criteria. Now for the
important part: Double click on the line between the two tables until a
dialog box shows up. Click on option 2. Run the query and see if it works.

When the line is solid across, it is option 1 which is an inner join. Both
tables need a matching record to be returned. Option 2 is a Left Outer Join.
You might notice a little arrow on the joining line. It will return records
from the parent table even if the child table doesn't have any matches.
Option 3 is a Right Outer Join where child records will return even if there
isn't a parent.

The Null in the criteria prevents those records from displaying that have
matching records in both tables.

 

[Emma]

Thank you both it's working beautifully

Sure thing. Create a query with both tables joined. Bring down all the needed
fields from the main table that you want to see. Next bring down one field
from the case table. In that field put Null in the criteria. Now for the
important part: Double click on the line between the two tables until a
dialog box shows up. Click on option 2. Run the query and see if it works.

When the line is solid across, it is option 1 which is an inner join. Both
tables need a matching record to be returned. Option 2 is a Left Outer Join.
You might notice a little arrow on the joining line. It will return records
from the parent table even if the child table doesn't have any matches.
Option 3 is a Right Outer Join where child records will return even if there
isn't a parent.

The Null in the criteria prevents those records from displaying that have
matching records in both tables.