Membership databases
Galpin Society
Cardbox is used to keep the membership records of the Galpin Society. This society was founded for the study of old musical instruments and has about 850 active members. There is an annual subscription, which varies according to the type of membership and country of residence. Unusually the subscription year begins on 1st April rather than the 1st January. Many institutional members subscribe through agents. There are others who are sponsored in most cases by another member.
For each subscription year the member is entitled to three newsletters and one journal. Strictly speaking subscriptions are due on 1st April - that is at the beginning of the subscription year. In reality, if they do not pay on time, members will continue to receive newsletters but they will not get the journal which is published at the end of the subscription year unless they have actually paid up.
Each member is given a unique membership number. As well as serving as an identifier, this number is used to link the two principal files New Galpin and Payments.
New Galpin holds the details of each member as follows:
| Status | This is the first letter of the member’s current status: eg. A for active, D for dead. |
| Lastyr | This is the last year starting 1st April for which the member has paid their subscription. |
| Spon (or Agent) | This field is completed if the member comes through an agent or is sponsored. |
| Agref | The agent's reference if there is one. |
| Join | The year of joining. |
| Member No | |
| Mem Category | Type of membership. |
| Mem Country | |
| Sent | These boxes indicate which bulletins, newsletters or journals have been sent to each member. |
| Include | An "i" is entered here if the member has agreed for his email address to be included in the membership list. |
Payments, which is linked to New Galpin by the membership number, contains six fields as follows:
| Memno | This doesn't need to be displayed but its presence reassures the user that the relational link is still active. |
| Lastyr | This is the year which the payment covers or, if the payment is for more than one year, the last year which the payment covers. |
| Paydate | The date of the cheque, standing order, card payment, etc. |
| Cc | Y for sterling, N for US dollars. |
| Amount | |
| Type of Payment | A single letter which denotes cheque, standing order, etc (see screen copy above). |
| No | The number entered here is what might be termed a batch number. This enables the payments for a given "span" to be grouped together for banking purposes. When payments go straight into our Lloyds or Girobank account, this field is left blank. |
New Galpin and Payments are the most vital files in the database. However there are several others as follows:
Subscriptions contains subscription rates and is linked to both New Galpin (via type and country of membership) and Payments (via currency). It is an aide memoire for the subscription rate. That is why a big bold font is used.
Expenses contains details of expenditure by the membership secretary and treasurer which, among other things, enables me to work out my expenses.
Lloyds contains the individual transactions on our main bank account.
Girobank contains the individual transactions on our Girobank account.
Emails is used to store all outgoing correspondence together with incoming emails.
Postdats stores the dates that Journals and newsletters, etc., were sent out and also the dates that batches of cheques, etc., were taken to the bank.
Journals This stores the numbers and quantities of journals that we still have in stock.
PROCEDURES
New member joining
The particulars are entered with the latest membership number plus one and the status is entered as A. If they are a sponsored member then the sponsor is entered too – the first letter of surname capitalized and the remaining four or fewer letters in lower case. If the member comes through an agent who wants all journals and newsletters sent directly to their client then the agent’s acronym in upper case must be entered together with the reference if they have one.
Subscription Payments
Each time a payment or payments come in I check that the address(es) shown on the payment bumph correspond with those on the database, etc., etc. Once I am confident that all is well I press F11 which initiates a macro which ‘asks’ you for the year, payment date, currency (Y or N), amount, type of payment and batch number. This then updates the member’s record and copies the membership number. It then creates a new record for this member in the payments file and returns to the main file.
Paying subscriptions into the bank
At least once month I run a macro that exports the entire membership file and then asks for a batch number. It then exports all the payments with that batch number. Once I have this information outside Cardbox I use other software to produce an ordered list which gives member’s names with payment details together with a total amount. I then update Postdats entering the date taken to bank and batch number.
Chasing subscriptions
Over half the membership pays (directly or indirectly) without prompting. The remainder have to be reminded. For this I use a form using mail merge.
Mailings
Four times a year labels are required for the three newsletters and one journal. These are a doddle with Cardbox.
Our thanks to Augustine Ford for providing this very detailed case study.
Wilhelmshaven Association
This is the alumni association of a school in Germany. The Cardbox database is mainly a record of addresses and details of years at school , the house they were in and who in particular they would like to get in touch with. There is a membership fee so Cardbox is used to record who has paid and then used to produce labels for the newsletters that are sent out three times a year. Cardbox is also used for mail-merging into a word processing package to produce standard letters such as reminders for subscriptions or to let people know that friends have been found.
Every year the association produces a list of all the 3,200+ names that are on the database to send out to members.
The association has a reunion every two years and Cardbox is used to record who has paid, and if they are bringing a guest - their name as everyone attending has to have a security badge with their details on. The association also sells merchandise - polo shirts, car stickers, mugs and now a book - so again Cardbox is used to record those details on the database.
The Cardbox search facilities are used all the time - when someone new turns up Cardbox is used to search to see if someone has specifically asked for them and is so fast that this can be done whilst the new member is on the phone. The Batch Edit facility is used when updating the details on membership when the subscription has been paid.
Our thanks to the Wilhelmshaven Association for providing this case study.