Solved

Query to report records with no qualifying attcahed records

Posted on 2014-01-23
8
263 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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