Compare Records in the same table

  • Thread starter Thread starter Ray magill
  • Start date Start date
R

Ray magill

I have a orders table.
CustNum, ordType
1,a
2,a
1,b

I want to return all CustNum's that have an ordType of a that don't have
any other orders that have a type of b
2,a
I know I can do this with Multiple querys, is there a way to do this with a
single query of statement
Thanks
 
This may not be syntactically correct, but it would be something like:

SELECT CustNum, ordType FROM OrderTable WHERE ordType = "A" AND CustNum NOT
IN (SELECT CustNum FROM OrderTable WHERE ordType = "B")
 
This may not be syntactically correct, but it would be something like:

SELECT CustNum, ordType FROM OrderTable WHERE ordType = "A" AND
CustNum NOT IN (SELECT CustNum FROM OrderTable WHERE ordType = "B")
Click to expand...

You da man
Thanks
 
Just in case there were other ord types besides a and b you might change the
criteria in the subquery to
WHERE OrdType <> "A"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Back
Top