Solved

Query to report records with no qualifying attcahed records

Posted on 2014-01-23
8
254 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
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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 40

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access MDB/PDF 21 32
How to simplify my SQL statement? 14 23
MySQL left join performance 4 14
DATETIMEOFFSET feature 1 0
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now