Data Files samples / Charity or club
Rowanmere Community Trust
Members, supporters, gifts, tax claims, events, volunteers and grants for a small community charity or club.
Queries
The questions this database answers, saved under a name. Open one to see its answer, or its design to see how it is built and change it.
- Current members
Everyone whose membership has started and not yet ended, and life members, by type. Use it to check who may vote at the AGM or use members' rates.
From Memberships, People, Membership Types. Shows Type, Member, Starts, Ends, E-mail, Phone.
Calculated: Member is
[First Name] & " " & [Last Name].Only where
Days In >= 0andDays In >= 0andDays Left >= 0andEnds Is Null.- Members by type
How many current members there are of each type and what they paid. A quick count for the committee.
From Memberships, Membership Types. Shows Type, Members, Fees Paid, with totals.
Only where
Days In >= 0andDays In >= 0andDays Left >= 0andEnds Is Null.- Renewals due
Memberships ending in the next 30 days that have not been renewed, with addresses and the fee to ask for. Run it each week and send the reminders.
From Memberships, People, Membership Types. Shows Ends, Days Left, Member, Type, Renewal Fee, Address, City, Postcode, E-mail, Contact by E-mail.
Calculated: Days Left is
DateDiff("d", Date(), [Ends]); Member is[First Name] & " " & [Last Name].Only where
Days Left Between 0 And 30andRenewed = No.- Lapsed members
Members whose membership ended in the last year and who have not renewed. Worth a friendly call or e-mail; some simply forgot.
From Memberships, People, Membership Types. Shows Member, Type, Ended, Days Lapsed, E-mail, Phone, Contact by E-mail.
Calculated: Member is
[First Name] & " " & [Last Name]; Days Lapsed isDateDiff("d", [Ends], Date()).Only where
Days Lapsed Between 1 And 365andRenewed = No.- Appeals against target
Each appeal and fund with its target, what has been given to it and how many gifts. Shows which appeals reached their target.
From Campaigns, Donations. Shows Campaign, Opens, Closes, Target, Given, Gifts, with totals.
- Gifts to claim
Claimable gifts not yet put into a tax claim, with the giver's name, house and postcode and what the claim on each brings back. This is the next claim.
From Donations, People. Shows Gift Date, First Name, Last Name, Address, Postcode, Amount, Claim Value.
Calculated: Claim Value is
Round([Amount] * 0.25, 2).Only where
Claimable = YesandTax Claim Is Null.- Tax claims made
Each tax claim with the gifts in it, what was claimed and when the money came; a claim still awaited has no date received.
From Tax Claims, Donations. Shows Reference, Gifts Up To, Submitted On, Gifts, Gifts Total, Claimed, Received On, Received, with totals.
- Top supporters
The people who gave most in the last 12 months, with the number of gifts. Use it for thank-you letters.
From People, Donations. Shows Supporter, City, Given, Gifts, Tax Declaration, with totals.
Calculated: Supporter is
[First Name] & " " & [Last Name].Only where
Days Ago Between 0 And 365.- Regular givers
Supporters with a monthly gift that arrived in the last 45 days, and the amount. A giver missing from it may have stopped their standing order.
From People, Donations. Shows Supporter, Monthly Gift, Last Gift, Tax Declaration, with totals.
Calculated: Supporter is
[First Name] & " " & [Last Name].Only where
Regular = YesandDays Ago Between 0 And 45.- Volunteer hours by month
Hours given each month in each role, and their value at the role's hourly rate. Funders and annual reports often ask for this.
From Volunteer Shifts, Volunteer Roles. Shows Month, Role, Hours, Value, with totals.
Calculated: Month is
Format([Shift Date], "yyyy-mm"); Value isRound([Hours] * [Hour Value], 2).Only where
Days Ago >= 0.- Volunteers
Each volunteer's shifts and hours in the last 12 months and their last shift, most hours first.
From Volunteer Shifts, People. Shows Volunteer, Phone, Shifts, Hours, Last Shift, with totals.
Calculated: Volunteer is
[First Name] & " " & [Last Name].Only where
Days Ago Between 0 And 365.- Volunteer rota
Who is on which shift in the next 14 days, with a phone number, for the rota sheet on the noticeboard.
From Volunteer Shifts, Volunteer Roles, People. Shows Shift Date, Start Time, Role, Volunteer, Phone, Hours.
Calculated: Volunteer is
[First Name] & " " & [Last Name].Only where
Days Ahead Between 0 And 14.- Upcoming events
Events in the next 60 days with tickets booked so far against the capacity and the money taken.
From Events, Event Bookings. Shows Event Date, Event, Start Time, Venue, Capacity, Booked, Taken, with totals.
Only where
Days Ahead Between 0 And 60andCancelled = No.- Event attendance
Each event held, with tickets booked, how many came and the ticket income against the costs.
From Events, Event Bookings. Shows Event Date, Event, Capacity, Booked, Came, Ticket Income, Costs, with totals.
Calculated: Came is
IIf([Attended] = 1, [Tickets], 0).Only where
Status = HeldandCancelled = No.- Guests booked
Every booking for events in the next 60 days, by event, for the door list.
From Event Bookings, Events, People. Shows Event, Event Date, Guest, Tickets, Amount Paid, Phone.
Calculated: Event is
[Events].[Name] & ", " & Format([Event Date], "yyyy-mm-dd"); Guest is[First Name] & " " & [Last Name].Only where
Days Ahead Between 0 And 60andCancelled = No.- Grant reports due
Grants awarded whose report to the funder is not yet sent, soonest first; a negative Days Left means the report is late.
From Grants, Funders. Shows Funder, Purpose, Amount Awarded, Period Ends, Report Due, Days Left, Contact Name.
Calculated: Days Left is
DateDiff("d", Date(), [Report Due]).Only where
Status = AwardedandReport Sent Is Null.- Grants by funder
For each funder, how many applications were made, how much was asked and how much awarded.
From Grants, Funders. Shows Funder, Kind, Applications, Asked, Awarded, with totals.
Calculated: Awarded is
Nz([Amount Awarded]).- Income by source by month
All income each month by source: membership, donations, events, collections, grants and tax claims.
From Income. Shows Month, Source, Received, Payments, with totals.
Calculated: Month is
Format([Received On], "yyyy-mm").- Income by source
Income in the last 12 months by source, largest first, for the treasurer's report.
From Income. Shows Source, Received, Payments, with totals.
Only where
Days Ago Between 0 And 365.- Mailing list
Everyone who agreed to post, with their address, sorted by name, for the newsletter labels.
From People. Shows Last Name, First Name, Address, City, Postcode.
Only where
Contact by Post = Yes.
Forms
Every table has a form of the same name: Start Here, Business, People, Membership Types, Memberships, Campaigns, Tax Claims, Donations, Events, Event Bookings, Volunteer Roles, Volunteer Shifts, Volunteer Certificates, Funders, Grants, Income. A form shows one record at a time and adds new ones. A field that points at another table is a list of that table's records, and the records that point at this one are listed below it.
Reports
Pages to print or save as a PDF, laid out from a query or a table.
- Membership list
The current members by type, with dates and contact details, and a count of each type. For the membership secretary.
From the query Current members, a group for each Type: Member, Starts, Ends, E-mail, Phone, with the count of Member.
- Renewal reminders
Members due to renew in the next 30 days with their address and fee, to write or print the reminders from.
From the query Renewals due: Member, Address, City, Postcode, Type, Ends, Renewal Fee, with the total of Renewal Fee.
- Donations by campaign
Each appeal and fund with its target, the number of gifts and the total given. For the trustees' meeting.
From the query Appeals against target: Campaign, Opens, Closes, Target, Gifts, Given, with the total of Given, total of Gifts.
- Gifts for the next tax claim
The gifts for the next tax claim with each giver's name, house and postcode, date and amount, and the total to claim. Keep it with the claim.
From the query Gifts to claim: First Name, Last Name, Address, Postcode, Gift Date, Amount, Claim Value, with the total of Amount, total of Claim Value.
- Volunteer hours
Volunteer hours and their value by month and role, with totals. For the annual report and grant reports.
From the query Volunteer hours by month, a group for each Month: Role, Hours, Value, with the total of Hours, total of Value.
- Event guest list
The guests booked for each event in the next 60 days with their tickets, for the door.
From the query Guests booked, a group for each Event: Guest, Tickets, Amount Paid, Phone, with the total of Tickets, total of Amount Paid.
- Income by month
Income by source for each month with the month's total. For the treasurer.
From the query Income by source by month, a group for each Month: Source, Payments, Received, with the total of Received.
- Grant reports to write
Grant reports still to write, with the amount and the date each is due.
From the query Grant reports due: Funder, Purpose, Amount Awarded, Report Due, Days Left, with the total of Amount Awarded.
- Start here: the everyday tasks, step by step
The guide to this database printed: each everyday task and its steps, from the Start Here table.
From the table Start Here, a group for each Task: Step, What to do.
Print templates
What the business sends out, one for each record, with its own name, address and payment details at the head from the Business table. Print one from its form, or choose which to print under Print templates, or save them as a PDF. The words in braces are filled in from the record.
Donation receipt
The thank-you letter for a gift, which is also its receipt: the amount, the day it came, the appeal it goes to, whether the giver has a tax declaration, and what the tax claim adds to it. Every gift has one; print those of the last week or two by choosing them, and leave out standing orders, which are thanked once a year.
One for each record of Donations, on A4 paper.
Thank you for your gift
To · Receipt · Address · Received on · Town · Given to · Postcode · Tax declaration
Dear {Person.First Name},
Thank you for your gift of {Amount}, which reached us on {Gift Date}. It goes to the {Campaign}: {Campaign.Description}.
Your gift · Tax we can claim back on it · What your gift is worth to us
Where Tax declaration says Yes, you have told us that you pay enough UK income tax for us to claim tax back on your gifts. We will put this one in our next claim, which adds a quarter to it at no cost to you; please tell us if your tax changes. Where it says No, we cannot claim on this gift. If you do pay UK income tax, a short declaration lets us claim on this gift and on your future ones: ask us for the form.
Please keep this letter as your receipt. Our newsletter tells you how the money is spent; let us know whether you would like it by post or by e-mail.
With warm thanks,
Signed: Treasurer
Registered charity number {Business.Company Number}. {Business.Document Footer}
Renewal reminder
A short letter to a member whose membership is ending: the fee for next year, how to pay, and what membership gives. Choose the memberships in the Renewals due query to print; a life membership has no end and needs no reminder.
One for each record of Memberships, on A4 paper.
Your membership is due for renewal
To · Membership no · Address · Membership · Town · Ends on · Postcode · Fee for next year
Dear {Person.First Name},
Your {Membership Type} membership of {Business.Name} comes to an end on {Ends}. We hope you will stay with us for another year. The fee for the year ahead is {Membership Type.Fee}.
Your fee last year was paid by: {Method}. If you pay by standing order, your membership renews itself on the day and there is nothing you need to do, unless your bank details have changed. Otherwise you can pay by card or cash at the hall office, or by bank transfer to {Business.Bank Details}, quoting your membership number, {Number}.
Members pay members' rates for events and the junior sessions, and have a vote at the annual general meeting. Your fee also helps to keep the hall warm and open for everyone who uses it.
If you have renewed since this letter was printed, thank you, and please ignore it. If you would rather not renew, you need do nothing, though we would be glad to know why: write to {Business.E-mail} or call {Business.Phone}.
With best wishes,
Signed: Membership Secretary
Registered charity number {Business.Company Number}. {Business.Document Footer}
Membership card
A small card for each membership, to show for members' rates: the member, the membership number, the kind of membership and the dates it is good for. Print it for a new or renewed membership.
One for each record of Memberships, on a till roll 58 mm wide paper.
Membership card
Member · Membership no · Membership · Valid from · Valid until
Show this card for members' rates at events. A life membership has no end date.
If found, please return to {Business.Name}, {Business.Address}, {Business.City} {Business.Postcode}.
Event ticket
Sent or handed to each guest when they book: the event, when and where, how many it admits, what was paid, and a ticket number to check at the door. A cancelled booking prints with Cancelled: Yes.
One for each record of Event Bookings, on A5 paper.
{Event}
Ticket · Date · Name · Starts · Admits · Venue · Paid · Booked on · Cancelled
Please bring this ticket, printed or on your phone, and give the number at the door. Tickets are refunded only if the event is cancelled.
{Business.Name}, {Business.Address}, {Business.City} {Business.Postcode}. Registered charity number {Business.Company Number}.
Volunteer certificate
A certificate of thanks for each volunteer, with the hours and shifts they gave in the volunteer year and the roles they did. Handed out at the annual general meeting, and useful to a volunteer for a job or college application.
One for each record of Volunteer Certificates, on A4 paper.
Certificate of thanks
This certificate is presented to
{Person}
in thanks for the {Hours} hours given as a volunteer with {Business.Name}, over {Shifts} shifts, in the year from {Year Starting} to {Year Ending}.
Roles · Certificate · Most hours in · Presented on
Our work depends on people who give their time freely. Thank you for yours.
Signed: Chair of trustees, Volunteer coordinator
Tax claim schedule
The list of gifts that goes with each claim to the tax office: each giver's name, house and postcode, the date and amount of each gift and the tax back on it, with the totals. The form your tax office wants may differ: check what it asks for in your own country.
One for each record of Tax Claims, on A4 paper.
Tax claim schedule {Reference}
Claim · Charity number · Gifts up to · Tax office reference · Submitted on · Paid on
A line for each record of Donations: Date, Receipt, First name, Last name, House and street, Postcode, Amount, Tax back
Gifts · Tax back on them · Amount claimed
Every gift listed was made by a person who had a tax declaration in place on the day of the gift. Keep this schedule, with the declarations, as long as the tax office asks.
Back to the overview of Rowanmere Community Trust