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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FROM tblClients c LEFT JOIN tblJobs j
ON c.[Client ID] = j.[Client ID]
WHERE  j.[Client ID] IS NULL

Open in new window

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.