Expert guide
Nonprofit Donor Tracking Database in Microsoft Access
Know who gave, thank them promptly, and see who is slipping away before they do
Call us at +1 (385) 386-3860
Short answer: separate donors from gifts. Store people and organisations in a Donors table, every donation in a Gifts table with date, amount, fund, campaign and payment method, pledges and their payments separately, and acknowledgements with the date sent. Reports then give giving history, retention, LYBUNT and SYBUNT lists, campaign results and year-end receipts. For grant funding alongside donations, see our grant management Access database.
Many small nonprofits track donors in a spreadsheet with one row per donor and a column per year. It works until someone asks how many first-time donors gave again, which campaign raised the most from new supporters, or which donors gave last year but not this year. Those questions need gifts as their own records. Access is a good fit: structured, inexpensive, and familiar to many volunteers and staff.
The Tables
| Table | Key fields | Notes |
|---|---|---|
| tblDonors | DonorID, Type (Individual/Organisation), Name fields, Address, Email, Phone, HouseholdID, FirstGiftDate | One record per donor |
| tblGifts | GiftID, DonorID, GiftDate, Amount, FundID, CampaignID, Method, Reference, SoftCreditDonorID | Every donation |
| tblPledges | PledgeID, DonorID, PledgeDate, Total, Schedule, CampaignID | Promises |
| tblPledgePayments | Links a GiftID to a PledgeID | Shows pledge balance |
| tblCampaigns | CampaignID, Name, Type (appeal, event, grant), StartDate, Goal | Results by campaign |
| tblFunds | FundID, Name, Restricted (Y/N) | Designations and restrictions |
| tblAcks | AckID, GiftID, SentOn, Method, SentBy | Thank-you tracking |
| tblPreferences | DonorID, NoMail, NoEmail, NoPhone, Anonymous | Respect choices |
Data Entry That Stays Clean
A gift entry form that searches donors first and creates a new donor only if none matches.
Combo boxes for fund, campaign and method, so every gift is categorised.
Batch entry for appeals: enter a batch total first, then the gifts, and check they agree.
Duplicate check on name and postcode in the donor form's BeforeUpdate event.
Reports Every Nonprofit Needs
LYBUNT and SYBUNT lists
LYBUNT means "last year but unfortunately not this year"; SYBUNT means "some year but unfortunately not this year". These lists are the most valuable fundraising reports a small organisation can run.
qryLYBUNT (fiscal year = calendar year here):
SELECT d.DonorID, d.FullName, d.Email,
Sum(g.Amount) AS LastYearTotal
FROM tblDonors AS d INNER JOIN tblGifts AS g ON d.DonorID = g.DonorID
WHERE Year(g.GiftDate) = Year(Date()) - 1
AND d.DonorID Not In (SELECT DonorID FROM tblGifts
WHERE Year(GiftDate) = Year(Date()))
GROUP BY d.DonorID, d.FullName, d.Email
ORDER BY Sum(g.Amount) DESC;Donor retention
Retained donors (gave last year and this year): SELECT Count(*) AS Retained FROM (SELECT DISTINCT DonorID FROM tblGifts WHERE Year(GiftDate) = Year(Date()) - 1) AS LY INNER JOIN (SELECT DISTINCT DonorID FROM tblGifts WHERE Year(GiftDate) = Year(Date())) AS TY ON LY.DonorID = TY.DonorID; Retention rate = Retained ÷ count of distinct donors last year.
Calculate retention separately for first-time donors and repeat donors. First-year retention is typically much lower, and it is where a good thank-you process makes the biggest difference.
Campaign results
Total raised, number of gifts and average gift per campaign.
New donors acquired by the campaign (FirstGiftDate within the campaign period and gift coded to it).
Cost per dollar raised, if campaign costs are recorded.
Year-end receipts
A report grouped by donor for the tax year lists each gift with date and amount, and the total. Use DoCmd.OutputTo in a loop to save one PDF per donor, or send them by email with a short VBA routine. Check receipt wording requirements for your jurisdiction and organisation type.
Acknowledgement Workflow
qryUnacknowledgedGifts:
SELECT g.GiftID, d.FullName, g.GiftDate, g.Amount, f.Name AS Fund
FROM (tblGifts AS g INNER JOIN tblDonors AS d ON g.DonorID = d.DonorID)
INNER JOIN tblFunds AS f ON g.FundID = f.FundID
WHERE g.GiftID Not In (SELECT GiftID FROM tblAcks)
ORDER BY g.GiftDate;Show the count of unacknowledged gifts older than 48 hours on the database's start screen. Prompt thanks is one of the strongest drivers of a second gift.
Where AI Helps a Small Development Team
Thank-you letter drafts
Personalised from fund, campaign and giving history (for example, 'your third year supporting the food pantry'), reviewed before sending.
Segment summaries
A short narrative of LYBUNT donors by size and last campaign for the board.
Cleaning imported lists
Event sign-up sheets and online giving exports with inconsistent names and addresses.
Donor data is personal data; use approved tools and send only what is needed. The drafting pattern is described in personalized emails with AI, and the Access integration in connecting Access to an AI API.
Importing Online Donations
Online giving platforms and payment processors export CSV files. A saved import routine maps their columns to tblGifts, matches donors by email, and holds unmatched rows in a staging table for review. Running it weekly keeps the database complete without retyping.
Build Notes
Split the database so volunteers each have a front end.
Never delete gifts; reverse them with a negative adjustment and a reason.
Store anonymity and communication preferences, and honour them in every list query.
Back up nightly; donor history is irreplaceable.
We build donor databases like this as fixed-scope custom Access projects, with reports through our Access reports and forms service. For grant applications, reporting deadlines and restricted funds, pair it with the grant management database.
FAQ
Nonprofit donor database FAQ
Structure, retention and receipts