Charitable Contributions Templated

For three months this fall we were fortunate to have Anshul Tandon working on our PM team as an intern.  Anshul has now gone back to Louisiana State University where he's a member of the senior class. During his time with us one of his tasks was to create the new Charitable Contributions Template.  Here's more in his own words:

The new Access 2007 Charitable Contributions template is now ready. You can download it from Office Online or access it in the Featured Templates section of the Getting Started page in Microsoft Access).
I am interested in getting feedback on your experiences using this template. I’d love to see how you are using it and to discuss ways we can improve our templates.
Let me start by talking about this template. You can use it to:

• Manage several campaigns simultaneously
• Track fundraising events for a large campaign
• Track donations and pledges made by contributors
• Group donations by campaigns or events
• Assign tasks to your employees
• Delegate employees as primary contacts for campaigns, event, and contributors
• Set fundraising goals and track amount raised

I’ll explain some of the details of this template. For those of you who are familiar with other Access 2007 templates, I have used a lot of great ideas from them. These include exposing related forms and reports in the toolbar, using safe macros actions for all events, and displaying records in split-forms. I have also added the main home-page with links to improve the overall navigation.

Navigation

The template has an easy-to-use navigation homepage which provides quick links to the most commonly used forms and reports. As shown in the image below, it can be used to manage tasks as well. You can give feedback on the template using the "Provide Feedback" button in the quick links section of the page. The buttons on the header section provide access to view the list of all records for each table. I’ll explain the purpose of these tables later. Here’s a screenshot of the startup form.

Forms

As with all of our templates, we have a List form to show all records and a Details form to view details of a single record. This format allows easy access to view all records in a datasheet form or to zoom into the details of one of those records. The Details forms can be viewed by clicking on the link for a particular record in the List forms.
The Campaigns List and the Events List have a histogram to compare the fundraising goals for the ongoing campaigns and events. Related items are shown as sub-forms within the details form, for example, events and donations for a campaign. I’ve also added a new “Home” button in the header section of each List form. You can use this to navigate back to the main-page and to keep the number of open tabs low.
Here’s an example of the Campaign List form with the histogram. You can use the histogram to compare the total amount that you need to raise for each ongoing campaign.

Here’s what the Details form for a campaign looks like. The other detail forms follow the same format and layout for consistency.

Schema

The template consists of six tables: Campaigns, Events, Contributors, Donations, Fundraising Tasks, and Employees. The following image shows the relationships of these tables:

Here’s what the tables are for

• Campaigns table: tracks all the campaigns
• Events table: tracks independent events and those that are part of campaigns
• Contributors table: keeps a record of all the people who have already donated or are potential donors
• Donations table: tracks both unpaid pledges and paid donations
• Tasks table: keeps a track of all the tasks that need to be done for a given campaign or event
• Employees table: stores a list of all the employees of the organization that will use this database. It is meant to track the following:
  • The owner of a campaign/event/donation is the employee who is assigned to the campaign/event/donation. He/She is the "go-to" person and responsible for managing funds and contributors for that campaign.
  • The primary contact for a contributor is the employee who is responsible for maintaining the relationship with the contributor and is the main contact when dealing with the contributor.

Some interesting notes about the database. I’ve included these to try and answer some common questions you might have about the schema-design of the template.

1. Campaigns vs. Events: Events can either be assigned to campaigns or can be independent. Independent events are basically fundraisers that don't belong to a campaign but are held to raise money towards a goal. A campaign is a collection of events and donations. Contributors can fund a campaign by donating money at an event which was held for that campaign.
2. Managing donations: Donations can be made towards campaigns and/or events. Tracking donations that were made for events is now possible too. People who need to track donations but not campaigns can use this template by ignoring the Campaigns section.
3. Employees: Although employees are the primary contacts and owners of all the records, this field is not required in any of tables. If the organization does not want to track the employees or does not have many employees to track, they can ignore this field and use the rest of the database.
4. Constituencies: Contributors can belong to one or more categories (for example: parent, alumnus, charity, organization etc.) These categories are identified as constituencies for a contributor. The constituencies are stored as a multi-value field within the table. This enables sorting and categorizing contributors by the group they belong to.

Reports

The database contains the following reports:

• All Contributors: Lists all the contributors in the system
• All Donations: Lists all donations and pledges grouped by the campaign they belong to
• All Employees: Lists all the employees in the system
• All Events: List all events grouped by the campaign they belong to
• All Tasks: Lists all the tasks in the system
• Completed Campaigns: Shows all the Campaigns where Status = Completed
• Ongoing Campaigns: Shows all the Campaigns where Status <> Completed
• Pending Tasks: Shows all the Tasks where State <> Completed
• Unpaid Pledges: Shows all Donations where Paid <> True
• Upcoming Events: Shows all Events where Start Date >= Current Date

Here’s an example of the Pending Tasks report.

You can give feedback to Microsoft about your experiences using the template or about things you would like to see improved in templates by clicking on the “Provide Feedback” link on the main-page (shown below).

You can click on the “Take the Online Survey” link to go to a short online survey where you can tell us about your experience, rate the template, and write any comments or questions you might have.

Although Anshul's internship has come to an end, this feedback link is still live, and we'd still love to get feedback on the design of this template through the feedback form.  Thanks all, and happy templating!