select to compare

[Neillg]

I am working on a small database that is used for tracking donations.
I have two tables, one with donators ( name, address etc) and one with
donations ( amount, date).
I wish to compare the current month with the same month last year ( that is
to compare say march 2007 with march 2006) and from that extract those who
donated last year, but not this year, and also thos who donated this year,,
but not last year.
To further complicate this query, some people donate more than once a year,
so all those who have donated more than once in any of the past 12 months
need to be highlighted.
Is it possible to do in a single sql type query?
Any help would be greatly appreciated

Thanks

 

[Allen Browne]

Where you need to query multiple aspects from the same table, use a
subquery.

This example assumes a table named Donations with fields DonorID (who gave),
DonationDate (when given), and Amount (how much.) It groups by the donor,
and shows the January figures:

SELECT DonorID, Sum(Amount) AS ThisYearAmount,
(SELECT Sum(Amount) AS SumOfAmount
FROM Donations AS Dupe
WHERE Dupe.DonorID = Donations.DonorID
AND Dupe.DonationDate Between #1/1/2006# And #1/31/2006#)
AS LastYearAmount
FROM Donations
WHERE DonationDate Between #1/1/2007# And #1/31/2007#
GROUP BY DonorID;

No doubt you will need to adapt that to your actual tables.

Create another subquery to identify the count of donations for the donor in
a year.

If subqueries are new, see:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

 

[Neillg]

Thanks for your quick response. I will work on the subquery link first to
become fimiliar with it, although it does all make sense when I see it
written the way you have.

Again thanks

Neill

Where you need to query multiple aspects from the same table, use a
subquery.

This example assumes a table named Donations with fields DonorID (who gave),
DonationDate (when given), and Amount (how much.) It groups by the donor,
and shows the January figures:

SELECT DonorID, Sum(Amount) AS ThisYearAmount,
(SELECT Sum(Amount) AS SumOfAmount
FROM Donations AS Dupe
WHERE Dupe.DonorID = Donations.DonorID
AND Dupe.DonationDate Between #1/1/2006# And #1/31/2006#)
AS LastYearAmount
FROM Donations
WHERE DonationDate Between #1/1/2007# And #1/31/2007#
GROUP BY DonorID;

No doubt you will need to adapt that to your actual tables.

Create another subquery to identify the count of donations for the donor in
a year.

If subqueries are new, see:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

I am working on a small database that is used for tracking donations.
I have two tables, one with donators ( name, address etc) and one with

[quoted text clipped - 13 lines]

Thanks

 

[Dale Fye]

Another way to handle this might look like the following. You might want to
try both techniques to determine which would process quicker.

SELECT D.DonorID, D.LastName, D.FirstName,
SUM(IIF(Year(C.DonationDate) = Year(Date())-1), C.Contrib, 0)) as
PrevYrContrib,
SUM(IIF(Year(C.DonationDate) = Year(Date())-1), 1, 0)) as PrevYrTimes
SUM(IIF(Year(C.DonationDate) = Year(Date()), C.Contrib, 0)) as ThisYrContrib,
SUM(IIF(Year(C.DonationDate) = Year(Date()), 1, 0)) as ThisYrTimes
FROM Donors D
INNER JOIN Donations C
ON D.DonorID = C.DonorID
GROUP BY D.DonorID, D.LastName, D.FirstName

HTH
Dale