If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Interesting query requirement
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? |
#2
|
|||
|
|||
Interesting query requirement
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 Tempo.OrderStatus In ("Cancelled","Deleted") And Temp.AgencyName = SomeTable.AgencyName) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County h2fcell wrote: 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? |
Thread Tools | |
Display Modes | |
|
|