filter query using subtables

[Matt Edwards]

I'm trying to create a query that returns the contact info
for uncollected pledges. I want to track uncollected
pledges using a "pledge" subtable and a "donation"
subtable.

It seems like the easiest way to create the query would be
to display ContactID (the primary key), based on criteria
saying

(pledge <> " ") AND (donation = " ")

That just pulls up a whole lot of nothing.

Other times I try listing the fields, "pledge"
and "donation," where "donation = " " ". But instead of
pulling up all the donors for whom the donation is blank,
it pulls up all the donors who have donated (ie, the
COLLECTED pledges).

Any ideas?

-ME

 

[John Vinson]

I'm trying to create a query that returns the contact info
for uncollected pledges. I want to track uncollected
pledges using a "pledge" subtable and a "donation"
subtable.

It seems like the easiest way to create the query would be
to display ContactID (the primary key), based on criteria
saying

(pledge <> " ") AND (donation = " ")

That just pulls up a whole lot of nothing.

Other times I try listing the fields, "pledge"
and "donation," where "donation = " " ". But instead of
pulling up all the donors for whom the donation is blank,
it pulls up all the donors who have donated (ie, the
COLLECTED pledges).

Blank and NULL are *two different things*. " " means "A text string
containing a single x20 (Blank) character"; NULL means "this field is
empty, its value is undefined, unknown". The *only* criteria you can
use to find (or exclude) NULL fields are IS NULL and IS NOT NULL; a
NULL field is not equal to anything (because its value is unknown) -
it's not even UNEQUAL to anything (because its value could be
anything, you don't know).

Try a criterion of

Pledge IS NOT NULL And Donation IS NULL

to nab the scalawag who Promiseth and Performeth Not...