How do I query non-donors in Access?

[Guest]

I have an Access 2003 database that is used for our mailing list and keeps
donor records. How do I make a query that will tell me all of the donors
that have not given in the last five years? I was able to do a query that
gave me everyone that had donated more than $100 in the last five years but I
can't figure out how to find the donors that have not given. Any suggestions?

Thanks!!
Jenny Patterson
Shelter for Help in Emergency

 

[Guest]

Without knowing your table structure, I'll assume you have a Donors table and
a Donations table, both of which have a Donor_ID.

The following query uses what some call a "frustrated outer join" that
includes a nested subquery. The subquery is necessary to identify which
donors have contributed within the last 5 years. You could create this as a
stand alone query if you want, but I prefer to have all my SQL in a single
query if I can. Additionally, you could change the subquery to be a DISTINCT
query, but based on several recent discussions on this forum regarding the
speed of Distinct vs Group By, I have used the Group By clause to limit the
result of the subquery to a single instance of the Donor_ID.

The "frustrated" part of the join is that you are selecting all of the names
from the Donors table that don't have a matching record in the subquery.

If so, it would look something like:

SELECT Donors.LastName, Donors.FirstName
FROM tblDonors
LEFT JOIN (SELECT Donor_ID FROM Donations
WHERE Donation_Date > dateadd("yyyy", -5, Date())
GROUP BY Donor_ID) as RecentDonors
ON Donors.Donor_ID = RecentDonors.Donor_ID
WHERE RecentDonors.Donor_ID IS NULL

HTH
Dale