Non contributors

[John]

Hi,
I have a database of club members some of whoom make regular
payments/donations.I have a members table and a payments table and
associated forms.I need to identify members who have never contributed, and
want to write a simple query to do this. Since those who have never
contributed are consequently not entered in the payments table I need the
query to reflect this but am at a loss for how to do it.Anyone got any
ideas.Tia, John

 

[Ken Snell [MVP]]

The query wizard can do this for you; look at the Unmatched records option
in the "New Query" list.

Essentially, what you do is a LEFT JOIN between the ClubMember table and the
Payments table and look for members with no record in the Payments table:

SELECT ClubMembers.MemberID
FROM ClubMembers LEFT JOIN Payments
ON ClubMembers.MemberID = Payments.MemberID
WHERE Payments.MemberID Is Null;

 

[Al Camp]

John,
Your query should use the Members table, linked to the Payments table.
The link should be a Left Join that translates to...
"Show all records in Members and only those in Payments that match."
If you include a field from Payments like PaymentDate, those folks who
haven't contributed will have a Null PaymentDate.
Filter the query for PaymentDate = Null and you'll only return
non-contributers.