Thanks for the Complement: reversing a query result.

Monday, March 7, 2011

Thanks for the Complement: reversing a query result.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)

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?

The last choice is the one we want - create a new list. Choose this, pick the list folder under which you want to store this new list, and name it  --let's suppose you call it 2010Pmts. You will see the Progress Bar grow as the list is built.

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!

Get this widget!

Powered by Orchid Suites
Orchid ver. 4.7.6.