Hi, all! I’m trying to send off some information on donations to a fundraiser specialist, and I need to draw some information from our homebrew access database.
They want mailing information on everyone who’s donated in the last three years. No problem.
They want to know the date and amount of each person’s last donation. How do I write a query to pull this information? I’m thinking it’s a query that groups by donor_id, filtered by max.date, which will give me the date; but how do I get the amount in that?
They also want the date and amount of each person’s largest donation. How do I write a query to pull this information? I’m thinking it’sa query that groups by donor_id, filtered by max.amount, which will give me the amount; but how do I get the date in the query?
If you can explain how to build the Access query, that’d be great; if you can tell me how to write it in MSSQL, that’ll work, too.
Can you give us some info on how your database is structured? Or email me if you like.
Do you have a table of donors and a separate table of donations linked by donor id or do you have a flat table of donations allong with all details of the donor, or something else?
Ah, yes. This is a fairly common, though not simple, relational database problem. In access, it’s probably easiest to do with a cascaded series of queries, each building on and refining the last.
I’m going to start by assuming that you have a ‘donations’ table or some such, with a foreign key to the donor’s personal information, the amount, the date, and some sort of easy to work with primary key. (Autonumbers are fine for this sort of thing.)
First off, start with a fairly simple grouping query based on the donations table… group by the donor FK, and get the max of amount and the max of date. This will be useful for both of the problems you’ve mentioned… call it donorFacts for now. (Max of date would be the last donation date, of course.)
Next, start a new grouping query ‘biggies’, using both the base donations table and the donorFacts query. Join them with donorFK to donorFK, amount to max-amount, group by donorFK (either one), and get the max of donationID - or the min. (Max will break ties in favor of a larger donationID, min in favor of a smaller.)
This ‘biggies’ query will thus list every donorFK that has at least one donation, and the donationID of his donation with the largest amount. You can then use this biggies query, along with the donations table and the donors table, to list the full information on these largest donations, including when they were made and what the name of the donor is. (Join max-donationID from biggies to donationID from donations, and donorFK from donations to donorID from donors.)
You create the ‘recents’ query in the same way as biggies, except joining max-date to donation date… thus it gets the id number of the most recent donation made by that donor. (If donations are entered into an ascending primary key strictly in chronological order, you can skip that step, but I wouldn’t want to assume something like that casually. Somebody’s paperwork is always late getting punched into the database.)
I hope this helps. Here’s a SQL listing of some of the queries I used:
donorfacts:
SELECT donations.donorFK, Max(donations.donDate) AS lastdate, Max(donations.donAmount) AS maxamt
FROM donations
GROUP BY donations.donorFK;
biggies:
SELECT donations.donorFK, Max(donations.donationID) AS bigID
FROM donations INNER JOIN donorFacts ON (donations.donorFK = donorFacts.donorFK) AND (donations.donAmount = donorFacts.maxamt)
GROUP BY donations.donorFK;
largestDonations:
SELECT donors.*, donations.*
FROM (biggies INNER JOIN donations ON biggies.bigID = donations.donationID) INNER JOIN donors ON donations.donorFK = donors.donorID;
recents:
SELECT donations.donorFK, Max(donations.donationID) AS lastdon
FROM donations INNER JOIN donorFacts ON (donations.donDate = donorFacts.lastdate) AND (donations.donorFK = donorFacts.donorFK)
GROUP BY donations.donorFK;
latestDonations:
SELECT donors.*, donations.*
FROM (recents INNER JOIN donations ON recents.lastdon = donations.donationID) INNER JOIN donors ON donations.donorFK = donors.donorID;
Bippy, there’s two tables I’ll want to draw from. I think the relevant fields from each table will be:
INDIVIDUALS
Person_ID (primary key)
First_Name
Last_Name
etc. (I’ll want all the address fields, salutation fields, etc.)
DONATIONS
Donation_ID (primary key)
Donor_ID (foreign key to Person_ID table)
Date
Donation_Amount
Donation_Type (I’ll need this in the query to filter out “registration,” “grant,” and so forth–but I won’t be displaying this field in the query)
Donation_Occasion (Again, I’m going to need to filter pretty heavily in this one, but I don’t need to display the information in the query).
Ah–brilliant! This is the step I couldn’t figure out.
Instead of breaking ties by max of donation_ID, could I break ties by max of donation_date? This would give me the most recent large donation.
Similarly, in the “recents” query, would it work to break ties with max of donation_amount?
Occasionally someone will make multiple donations in a single day: they might make a Christmas gift to Angie for $50.00, and a Christmas memorial in honor of Fluffy for $75.00. In this case, for the most recent donation, I’d want to pull out the Fluffy memorial.
If you want to break ties by using a non-primarykey field, you will probably need to either (a) add another query to your query chain, or (b) join back to the root table using your two maximized fields (in this case, date and amount) in which case you need to be able to guarantee that there is no possibility of the same person making two donations of equal amount on the same date. The advantage of picking the highest or lowest primary key is that it is a single value that inarguably points to only one donation.
Good luck with your own experimentation!!
PS: For filtering fields (donation_type and donation_occasion) you’re going to have to repeat the same criteria on every query level until you’re joining based on a primary key value.
select i.first_name, i.last_name, d3.date, d3.max_amount
from (select donor_id, date, max(donation_amount) as max_amount
from donations d1
group by donor_id, date
where date = (select max(date) from donations d2 where d2.donor_id = d1.donor_id)) d3,
individuals i
where i.person_id = d3.donor_id;
Thanks–I set it up as you recommended, and it seemed to work quite well. I appreciate everyone’s help with this! I’ve been wondering how to do this for a few years now, but it only just now became necessary.