Query to report records with no qualifying attcahed records

Hello experts
I have a question regarding producing an Access query.  In simple terms I have a database with two tables – tblOrg and tblOpp
tblOrg has fields OrgID and Name
tblOpp has fields OID, OrgID, Title, Description and Active.

There is a one to many relationship (an Organisation can have many Opportunities).
I can create a query that shows all Orgs with any ACTIVE Opps (that’s a straightforward where clause)
But my question is – how can I construct a query that shows all Orgs that have NO active Opps. (nb – of course an Org can have Active and Inactive Opps and I don’t want them in the list)

I am sure this is simple, but I am stumped at the moment.

Regards

Richard
rltomalinAsked:
Who is Participating?
 
GrahamMandenoConnect With a Mentor Commented:
Hi Richard

Sorry - I inverted the logic for "Active".  The moral of the story is not to post answers at 11:23 pm at night :-)

Try this corrected version:
Select * from tblOrg
where not exists (Select OID from tblOpp 
    where tblOpp.OrgID=tblOrg.OrgID and tblOpp.Active<>0)

Open in new window


Best wishes,
Graham
0
 
SreeramCommented:
HI

Just try the Below code

Select * from tblOrg where OrgID not in (Select Distinct(OrgID)from tblOpp add your where condition to get the active record  )
0
 
mbizupCommented:
Try using an unmatched query.  This lists orgs that have NO corresponding active opp record:


SELECT *
FROM tbOrg a LEFT JOIN
 (SELECT OrgID FROM  tblOpp  WHERE Active = True) b 
ON a.OrgID = b.OrgID
WHERE 
b.OrgID IS NULL

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
GrahamMandenoCommented:
Hello Richard

Try a Not Exists query:
Select * from tblOrg
where not exists (Select OID from tblOpp 
    where tblOpp.OrgID=tblOrg.OrgID and tblOpp.Active=0)

Open in new window


Best wishes,
Graham Mandeno [Access MVP 1996-2014]
0
 
rltomalinAuthor Commented:
Thank you all three of you.
I have each solution on my little test db and only Graham's will run.  I appreciate that the other two just need a bit of perseverance, but I have currently settled on looking at Graham's

However, although it runs it does not give the correct result.

Here is a list of all the Opps in the db:
All the opps together with their Orgs.You can see from this that the result I want is just OrgID 7 - Relate.

But this is what Graham's code gives:
Graham's code resultI really can't figure out why that should be because the code looks quite easy to follow and (to me) makes sense.

Regards

Richard
0
 
SharathData EngineerCommented:
try this.
SELECT * 
  FROM tblOrg 
 WHERE EXISTS (SELECT OrgID 
                 FROM tblOpp 
                WHERE tblOpp.OrgID = tblOrg.OrgID 
                GROUP BY OrgID 
               HAVING SUM(IIF(Active = 0, 1, 0)) = COUNT(*)) 

Open in new window

0
 
rltomalinAuthor Commented:
Hi Sharath

Thanks for this suggestion.  I am out of the office now until Monday and will try it then.

Regards

Richard
0
 
rltomalinAuthor Commented:
Yes - this is just the job.

Thanks

Richard
0
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.