select to compare

N

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
 
A

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
 
N

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

Allen said:
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]
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top