asked on

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

Sreeram

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 )

mbizup

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

Graham Mandeno

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]

rltomalin

ASKER

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:

You can see from this that the result I want is just OrgID 7 - Relate.

But this is what Graham's code gives:

I really can't figure out why that should be because the code looks quite easy to follow and (to me) makes sense.

Regards

Richard

Sharath S

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

rltomalin

ASKER

Hi Sharath

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

Regards

Richard

ASKER CERTIFIED SOLUTION

Graham Mandeno

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rltomalin

ASKER

Yes - this is just the job.

Thanks

Richard