?
Solved

Query to report records with no qualifying attcahed records

Posted on 2014-01-23
8
Medium Priority
?
264 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
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 

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 2000 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.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

762 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