help with query calculations and summary

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

Jennifer

I need some help on how to get the info I'm looking for.

TblGift
ID(primary key)
DonorID
GDate (contains all gift dates for donors)
GAmount (contains corresponding amount for the gift)

I would like to develop a query or series of queries that
give me the total number of lapsed donors by year (i.e.
made a gift sometime in previous years - does not matter
how long ago - but not in past year); and how many of those
lapsed donors reactivated their support this year (by
making another gift).

Example of a lapsed donor that reactivates:
GDate
01/01/2000
01/01/2001 (Lapses starting in 2002)
01/01/2004 (Reactivates in 2004)

For example:

RenewalYear TotalLapsedDonors TotalRenewed
2001 1,000 300
2002 2,000 500
2003 3,000 900

Thanks in advance.
 
The technique to use is an Outer Join.

The hard part is assembling the data to be used in the OJ.

Manually, you could create a query of 2001 donors and 2002 donors, then
Outer Join the two queries to find those that donated in 2001, but not 2002.

qryDonors2001: Select * from tblGift WHERE Year(GDate) = 2001
qryDonors2002: Select * from tblGift WHERE Year(GDate) = 2002

qry2002Lapses: Select * from qryDonors2001 LEFT JOIN qryDonors2002 ON
qryDonors2001.DonorID = qryDonors2002.DonorID
WHERE qryDonors2002.DonorID is Null
 
Back
Top