By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

Querying joined tables for blank records

P: 3
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.
Feb 23 '07 #1
Share this Question
Share on Google+
6 Replies


100+
P: 176
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"
Feb 23 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
This should do it ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Clients.* 
  2. FROM Clients LEFT JOIN Calls
  3. ON Clients.ClientID=Calls.CallsID
  4. WHERE Calls.CallsID Is Null;
Mary
Feb 23 '07 #3

P: 3
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.
Feb 23 '07 #4

P: 3
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.
Feb 23 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 23 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 26 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.