Central Candidate Database Kick-off E-mail

On Tue, Jun 5, 2012 at 10:28 AM, Jim McIntosh <jim.mcintosh@sympatico.ca> wrote:

Hi Christin;

The attached file includes a description of the spread sheet (MEMLIST.xlsx) that I use to maintain the mailing list for members, inquiries and referrals. It should form the starting point for the requirements for a DB.

Assuming we are going to use an SQL-type DB, I anticipate we would likely have the following tables;

• Contact info (Name, phone(s), email, source, Membership Status, In Date, Join Date, Sign Date)
• Address (including Riding? We have several ‘couples’ with the same address, but this could be part of the Contact table)
• Donations (Date and amount of each donation)
• Officers ( Leader, Deputy Leader, Executives, Ethics Committee, Auditor, (etc?) - a 2-way, 1-1 link to the Contact info)
• Candidates (Election Date, Riding, Status, Endorsed, votes, %, Rank) (Rank would be like 4th of 7 candidates)
• Comments (free form text, possibly with tags/keywords to aid in searches)

Regards; Jim

(Relevant Material) from Original Attachement (Treasurer & CFO Duties)

MEMBERSHIP LIST (MEMLIST.xls) Information about members, people who have requested information about the Party, and people who have been referred to the Party by members is kept on a spread sheet named ‘Ont’ in an Excel file named MEMLIST.xlsx. There are several other spread sheets in this file.

• Contact info

• Name (including Prefix such as Mr. Ms., Dr., Prof. and Suffix such as CA, P.Eng), Salutation (if other than first name), address, telephone numbers, email ID, web site, Company name and Riding are maintained. Note: If a contribution is received from a corporation (or a union), the name of the Corporation, Company or union should go in the LASTNAME column.

• Status - The following Status codes are used;
  • I - Inquiry – an individual asked for information about the party.
  • R - Referral – Someone (usually a member) has asked us to send information to the individual
  • D - Donor – has made a contribution to the Party but has not joined.
  • M - Associate Member
  • V - Voting Member
  • X - Exchange – Someone we keep on the list indefinitely, either to exchange newsletters or as a friend of the Party
• Expiry Date – The date the individual’s membership in the Party expires, or the date the Inquiry or Referral will be removed from the mailing list.
• Contributions - There are four columns used to record Contributions;
  • Update - The date the donation was deposited in the Chequing Account
  • Deposit - The amount of the donation
  • Donation - Amount donated year-to-date not including the amount in the deposit column
  • LastDon - The date of the previous donation

Other Columns

• Comment - Used for a variety of purposes, such as identifying potential candidates
• Position - Used to identify members of the Executive and Ethics Committees and others active in the Party.
• Area - Identifies a riding as being in a geographic area such as Toronto East or Northern Ontario. Used to produce lists for regional coordinators to contact members and others on our mailing list.
• In Date - The date the individual asked to be added to the file. This may be the date on an Inquiry email sent from our web site or the date on a cheque or PayPal notification, or the date the person visited our booth at some event.
• Source - How the Party got this person’s name (web site, booth, another member)
• Join - The date the person became a member of the Party
• Sign - The date the member last signed the membership form, usually the same date as Join, but could differ if the person joined as an Associate Member first and later signed as a Voting Member. Members may also be asked to sign a new membership application if the criteria for membership are changed at a Convention.
• Update - The date of the last change to this person’s record. Depending on the Reason column, it could be the date: the information was added to the spreadsheet for a new inquiry or referral; the Donation was deposited; or the address was changed.
• # - Used to provide a count of the number of names on the list, or when the list is filtered. This column contains a “1” except for corporations.