Find entries in one table where no entry in second table

Good morning experts,

I have 2 tables (tblClients and tblJobs)
The relationship is One Client to MANY Jobs via [Client ID]

I wish to have a query that reports Clients with no jobs.
I have tried several things similar to:
Select [Client ID] from tblClients Where Dcount([Client ID], tblJobs) = 0;

but that seems not to work.

Can someone give me a clue please to how I could do this.

Best regards

Who is Participating?
mbizupConnect With a Mentor Commented:
That is the basic syntax for an Unmatched Query...

Since you are using Access, you can also use the Unmatched Query Wizard to walk you through building this.
FROM tblClients c LEFT JOIN tblJobs j
ON c.[Client ID] = j.[Client ID]
WHERE  j.[Client ID] IS NULL

Open in new window

rltomalinAuthor Commented:
Hi mbizup

That's awesome - thanks a bunch.  When you just pick up things as you go along you completely miss all this other stuff - never heard of an unmatched query!!

Anyway, used the wizard and it worked just fine.


Glad to help out!

Similar to your experience, for the most part I build my queries up from scratch - either in design view or in SQL view.  Doing so has made me completely miss the various query wizard options while I was learning how to build these queries.  I discovered the wizard options much later :-)

Another good one is the find duplicates wizard.
The one downside to using the query wizards though is that Access throws a lot of unneeded garbage -- extra parentheses, table prefixes, etc -- which can really clutter up your SQL View.

Something I do on occasion is build the query using the wizard to get the basic SQL Syntax, and then either clean up the generated SQL or use the wizard-built SQL as a template to build a new query without all of the ()'s and prefixes.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.