Interesting query requirement

[h2fcell]

I have an interesting query requirement that I’ve seem to gone blank on.
I’m querying a table with fields OrderNum, OrderStatus, AgencyName,
AgencyPhone.
OrderNum is the Primary Key, Indexed with (No Duplicates)
An Agency can have orders that have an OrderStatus that’s “Heldâ€,
“Invoicedâ€, “Cancelled†or “Deletedâ€.
I need to create a query that list the Agencies who have only had Orders
that where invoiced or held.
So if agency “A†has orders invoiced, held and cancelled or deleted, I don’t
need it on the list.
If agency “B†has only invoiced orders it needs to be on the list.
If agency “C†has only held orders, it needs to be on the list.
If Agency “D†has only invoiced or held orders, it needs to be on the list.

I’m confused about AND / OR in my Where.
Do I need to run separate queries?

 

[John Spencer]

The simplest query to do that would be

SELECT Distinct AgencyName
FROM SomeTable
WHERE AgencyName NOT IN
(SELECT AgencyName
FROM SomeTable
WHERE OrderStatus In ("Cancelled","Deleted"))

OR

SELECT Distinct AgencyName
FROM SomeTable
WHERE Not Exists
(SELECT Temp.AgencyName
FROM SomeTable as Temp
WHERE TemprderStatus In ("Cancelled","Deleted")
And Temp.AgencyName = SomeTable.AgencyName)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County