Find multiple record query

[brendoaccess]

Hi

I have two tables that record a one (Donor) to many (Donations) relationship. Most of the Donors in tblDonor make regular donations - each donation has a date, in tblDonations. Having recently undertaken a mailshot I have received many donations from Donors that had been inactive for several years, or had never donated.

The Mailshot was 1 December 08 - I want to know, for all Donations after this date, the length of time between that, and their previous donation (or have something tell me they are a first-time donor).

I can't get it at all - all help appreciated.

Many thanks.

B

 

[PHV]

tell me they are a first-time donor
What about this ?
SELECT [donor ID], Max([donation date]) AS LastDonation, Count(*) AS NumberOfDonations
FROM tblDonations
GROUP BY [donor ID]
HAVING Max([donation date]) > #2008-12-01# AND Count(*) = 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[brendoaccess]

Not there;

TblDonors has fields; DonorID, FirstName, Surname

TblDonations has fields; DonationsID, DonorID, DonationAmt, DonationDate

The query I want would need the 2 tables to identify the Donor making the donations.

I hope this is clearer.

Thanks

 

[PHV]

You don't know how to join tables ?
did you ever try to adapt my suggestion ?

 

[brendoaccess]

Yes, but all I got was a mess.

I'll have another go - sorry for the trouble.

 

[lespaul]

perhaps you'll find this helpful:

Understanding SQL Joins

for now:

SELECT tblDonations.[donorID], Firstname, Surname, Max([donation date]) AS LastDonation, Count(*) AS NumberOfDonations
FROM tblDonations
INNER JOIN TblDonors on tblDonations.donorid = tblDonors.donorid
GROUP BY [donorID]
HAVING Max([donation date]) > #2008-12-01# AND Count(*) = 1

Leslie

Have you met Hardy Heron?

 

[brendoaccess]

OK - got that - great, thanks a lot, lespaul. I know now how many donations have been made, and the date of the last donation. I can even tweak it for 'average donation' etc, which is incredibly helpful - thanks again.

Back to my original question - for a list of donors, how can I get the Date Difference between the most recent donation, and their last (previous) donation.

Many thanks

 

[lespaul]

well I can't think of how to get the one donation before the last!

Here's how to get the date of the last transaction:

SELECT DonorID, Max(DonationDate) As LastDonation FROM tblDonations GROUP BY DonorID

And here's how I would use that to get the number of days in between all the donations (typed, not tested):

SELECT DonorID, DonationDate, LastDonation, DateDiff(DonationDate, LastDonation) As DaysBetween  FROM tblDonations As D
INNER JOIN (SELECT DonorID, Max(DonationDate) As LastDonation FROM tblDonations GROUP BY DonorID) As A ON D.DonorID = A.DonorID

but I can't quite get my head around getting the previous before the latest donation.

HTH

leslie

 

[PHV]

SELECT D.DonorID, Max(DonationDate) As PrevDonation, LastDonation, LastDonation-Max(DonationDate) As DaysBetween
FROM tblDonations As D INNER JOIN (
SELECT DonorID, Max(DonationDate) As LastDonation FROM tblDonations GROUP BY DonorID
) As A ON D.DonorID = A.DonorID
WHERE DonationDate < LastDonation

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

SELECT D.DonorID, Max(DonationDate) As PrevDonation, LastDonation, LastDonation-Max(DonationDate) As DaysBetween
FROM tblDonations As D INNER JOIN (
SELECT DonorID, Max(DonationDate) As LastDonation FROM tblDonations GROUP BY DonorID
) As A ON D.DonorID = A.DonorID
WHERE DonationDate < LastDonation
GROUP BY D.DonorID, LastDonation

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886