Thanks for the Complement: reversing a query result.
Monday, March 7, 2011
I often have trouble
figuring out how to search for something that
isn't there. For example, how can I list
all the customers in our database who HAVE NOT
given us any money in 2010? Looking for
payments that did not fall in that year is
obviously not the way to do it... help?
Think of it this way. You'd have no trouble finding all the people who did make a payment to you in 2010, right? You'd do something like
Select CustName, CustID
From Transaction
Where (TxType in "Enclosed Pmt| Pmt on Acct") and (TxDate bt 01/01/2010&12/31/2010)
Distinct
So the real question is "If I know how to get a group of people, how can I get everyone instead of that group?"
We call this finding the "complement" of a set of records - and it's very easy to do in the Members Only Query. Just build a list the includes everyone in the first set. Then query for everyone how is NOT on that list. Here's how it's done:
After executing the query above, click the LISTS button on the ribbon.
This dialog will appear?
Now you can get everyone else just be querying for everyone who is not tagged as being on 2010Pmts.
Select ID, LFname, Email
From Person
Where not (PersonLists include 2010Pmts)
Give it a try! And be sure to experiment with the other list options, like removing the results fro another list. Useful for such search as "Find everyone who paid dues in 2008 or 9, but no in 2010!