Solved

Query to report records with no qualifying attcahed records

Posted on 2014-01-23
8
260 Views
Last Modified: 2014-01-27
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
0
Comment
Question by:rltomalin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 3

Expert Comment

by:Sreeram
ID: 39802561
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39802562
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
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39802563
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:rltomalin
ID: 39802694
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
 
LVL 41

Expert Comment

by:Sharath
ID: 39805691
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
 

Author Comment

by:rltomalin
ID: 39805813
Hi Sharath

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

Regards

Richard
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 500 total points
ID: 39806029
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
 

Author Closing Comment

by:rltomalin
ID: 39811454
Yes - this is just the job.

Thanks

Richard
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question