"Opposite" Query

AdamMVRRS

Registered User.
Local time
Today, 17:15
Joined
Sep 27, 2013
Messages
16
Hi All,

I was hoping that you could help me today. I'm trying to create a query that will eventually become a report. It's based off the back of a current query that I use, but I just can't seem to figure it out.

We are a training company, so the existing report tells our Health and Safety Manager how many live and suspended learners are in each care home. Learner Status = 1 (live) or 2 (suspended) respectively. This report does not show a care home if it has no learners. So if a candidate completed their qualification their status would become 0 (exited) and the home would disappear off of the report.

That report looks great and works fantastic. But he is now asking for a report that lists all of the care homes that have NO live or suspended learners in (dormant employers). So it's pretty much the opposite of the above query.

I just can't seem to figure out a way for it to say "care homes that have NO 1's or 2's in".

I tried creating a query that lists exited learners but that didn't work as care homes with and without live learners came up on that.

I hope I explained it relatively simply - if you need any more information I'll be happy to supply it.

Thanks for any help!

(Access 2007 by the way)
 
Create a query that shows all the homes with 1's or 2's if you don't already have one, then use the unmatched query wizard to compare that query to the list of all homes.
 
You can create a simple join using 'Is Null' as criteria on the inner joined table.
 
Create a query that shows all the homes with 1's or 2's if you don't already have one, then use the unmatched query wizard to compare that query to the list of all homes.

You can create a simple join using 'Is Null' as criteria on the inner joined table.

Thank you - both work! I knew there was a simple way to do it :rolleyes:
 

Users who are viewing this thread

Back
Top Bottom