Querying joined tables for blank records

[SNOWcondor]

Hi,

I'm having trouble with a query and have been searching through numerous sites but am struggling to find anything or know exactly what to look for. Here's the scenario.

I have two tables, Clients and Calls, related by a unique field in the Clients table of "Client ID".
As calls are made to the clients the Calls table fills up with each record including the "Clients.Client ID" number. My problem is I would like to make a query that shows fields from the Clients table that have NO related records in the Calls table (ie that have no "Client ID" number in the Calls table).

When I make a query with these two tables it shows up with nothing in the datasheet view (as currently there are no related records in the calls). I need it the other way around. To show all fields in the Clients table with no related records.

I have Access 2003 on Windows XP. I have limited experience with coding and what I do know is old and stuffed in the back of my brain but welcome any suggestions with or without code, if this even possible.

I hope this makes sense to you and thanks for your help in advance.

[Michael R]

When I make a query with these two tables it shows up with nothing in the datasheet view (as currently there are no related records in the calls). I need it the other way around. To show all fields in the Clients table with no related records.

Add the following line to your query cretiria. (Change the names in the line if necessary)
DCount("Call_Id","Calls","Client_Id=" & [Client_ID])="0"

[MMcCarthy]

This should do it ...

SELECT Clients.* 
FROM Clients LEFT JOIN Calls
ON Clients.ClientID=Calls.CallsID
WHERE Calls.CallsID Is Null;

Mary

[SNOWcondor]

Thanks for your help so far but I'm sorry you'll have to bare with me. Michael R, I tried your code but wasn't sure which Client ID was refering to which table and exactly how to design the query to accommodate. If it helps I'll clarify my tables:

CLIENTS
*Client ID (relation:1)
Business Name
Contact
Phone Number
etc.

CALLS
*Call ID
Client ID (relation:many)
Date of call
etc.

mmcarthy, sorry I'm a bit undereducated/rusty. Where do I put your code?

Thanks again.

[SNOWcondor]

Wait don't worry. I found it. mmccarthy, I found where to put your code and with a bit of playing it worked. Thank you so much. I just have to be careful not to put spaces in field names when using SQL.

[MMcCarthy]

Wait don't worry. I found it. mmccarthy, I found where to put your code and with a bit of playing it worked. Thank you so much. I just have to be careful not to put spaces in field names when using SQL.

True. To get around it just put square brackets around the field or table name like [Client ID].

Glad it's working for you.

Mary

[NeoPa]

Wait don't worry. I found it. mmccarthy, I found where to put your code and with a bit of playing it worked. Thank you so much. I just have to be careful not to put spaces in field names when using SQL.

You're right though. Although you can get around it with the square brackets ([]) it is much better practice to avoid spaces and punctuation characters in names.