help with query calculations and summary

[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.

 

[[MVP] S.Clark]

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