Use count function to ignore duplicate entries and create new vari

[Abby]

sI have a database that records people and donations they make over time.
There are two table 1. Donors (with fields: ID, last name, first name) and 2.
Donations (with fields: ID, gift date, gift amount) I want to be able to
calculate the total number of first time donors per year, but i can't figure
out how to do this.
I think I need to do something like write a code that only counts the first
entry per ID (person).
any help would be greatly appreciated.
thanks

 

[dhurwyn]

My Hope is you have set up your tables with a 1 to many relationship on
donorid and not used the ID field of both tables to be the same field.
so that said

tblDonors tblDonations
DonorID DonationID
Lastname DonorID
Firstname GiftDate
gift amount


SELECT DISTINCTROW Count(tblDonors.DonorID) AS CountOfDonorID
FROM tblDonors INNER JOIN tblDonations ON tblDonors.DonorID =
tblDonations.DonorID
GROUP BY tblDonations.Giftdate
HAVING (((tblDonations.Giftdate) Between #1/1/2004# And #12/31/2004#));

this will give you a start for your query and you can go from there.
Demi

 

[John Spencer]

To get the count of new donors (those that have not given in prior years)
for any one year you would need a query like the one below. THis may be
quite slow. To make it faster you could create separate queries and then
combine them.

Select Count(Donors.ID)
FROM Donors
WHERE Donors.ID IN
(SELECT Donations.ID
FROM Donations
WHERE Donations.GiftDate Between #1/1/04# and #1/31/04#
AND Donations.DonorID NOT IN
(SELECT Dupe.DonorID
FROM Donations as DUPE
WHERE Dupe.GiftDate < #1/1/04#))

QThisYearDonors
SELECT Donations.ID
FROM Donations
WHERE Donations.GiftDate Between #1/1/04# and #1/31/04#

QPriorYearDonors
SELECT Dupe.ID
FROM Donations as DUPE
WHERE Dupe.GiftDate < #1/1/04#

NewDonors
SELECT QThisYearDonors.ID
FROM QThisYearDonors LEFT JOIN QPriorYearDonors
On QThisYearDonor.ID =QPriorYearDonors.id
WHERE QPriorYearDonors.ID is Null

Final Query
SELECT Donors.*
FROM Donors INNER JOIN NewDonors