filter query using subform

[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

 

[Jonathan Parminter]

-----Original Message-----
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

Hi Matt,
You may have to lookup join types. You can use the query
wizard, find unmatched records. That is there is a record
for the contact and this contact has a matching record in
the pledge table ('yes' I'm feeling guilty here <smile>)
but the pledge table has no matching record in the
donations table (even more guilty <blush>).

If this is the case then in the design view of your query
have the 3 tables. Contact and pledge linked by the
contactID, pledge and donation linked by pledgeID. With
the tip of the Pointer right-click the join between pledge
and donation. Select the join type that shows all records
in the pledge table and those in the donation table that
match. Then have the criteria for the donation.pledgeid
field as IsNull.

Luck
Jonathan