query help

[Jennifer]

Hello,

I am running multiple queries right now to get some data
totals, and am hoping to be able to simplify things (I
won't list all queries I'm using, but rather will try to
explain the end results I'm looking for).

I have 2 tables: TblDonor & TblGift

TblDonor contains
DonorID (primary key)
FGDate(first gift date)
FGAmount (Currency of first)
LGDate (last gift date)
LGAmount (Currency of last)
LrgGDate(Largest gift date)
LrgGAmount (Currency of largest)
*I know that these gift fields could be calculated directly
from TblGift - but it takes too much time to run queries,
so they're stored here for ease of analysis.

TblGift
ID (primary key)
DonorID
GDate (gift date - contains all gifts for donors)
GAmount (gift amount)

I would like one query that shows me the total number of
1st time donors in one year, and the total number that also
gave the next year...

Year Total New Renewed Next Year
2002 9,000 6,000
2003 10,000 8,000

And, I would like to create another query that shows me the
total number of donors (not just 1st time) who gave in one
year and those who gave in the next year.

Thanks in advance.
Jennifer

 

[ChrisJ]

The scheme below is untested. If it is not quite right, at
least it should give you the general approach.

Qry1
Select donorId, min(Year(GDate)) as firstYear from tblGift
group by donorId

This give a list of donors and their first donation year

qry2
Select Year(GDate) as DonationYear from tblGift group by
Year(GDate)

This gives you a list of years in which donations were
made as effectively makes your first column

qry3
Select firstYear, count(DonorId) as firstYearCount from
qry1 Group By FirstYear

This gives you your second column


qry4
Select tblGift.donorId, year(tblGift.GDate) as secondYear
from tblGift inner join qry1 on tblGift.donorId =
qry1.DonorId
where year(tblGift.GDate) = qry1.firstYear + 1

This gives you a list of donors who also made a donation
in the following year

qry5
Select secondYear, count(DonorId) as secondYearCount from
qry4 Group By secondYear

This gives you your third column

qry6
Select qry2.DonationYear, qry3.firstYearCount,
qry5.secondYearCount from
qry2
left join qry3 on
qry2.donationyear = qry3.firstyear
left join qry5 on
qry2.donationyear = qry5.secondyear