On Sep 3, 2008, at 10:14 AM, rogeradams wrote:
Ok. What I am trying to achieve is a database(file?) from which I
can draw the following reports:
1. A basic list with names, addresses, membership type (founder,
invested, joining) and membership basis (showing
resident, non-resident and resigned) and contact information only
2. A founder members list showing the dates they resigned (If they
resigned)
3. All-Time Members List (a historical record showing joining,
investiture and resignation dates using radio buttons
in the membership type field to identify each type)
4. A separate resignation list with dates of resignations
5. Current Members List (showing member ID, first name, last name
(membership basis e.g. joining, invested and
resigned, using radio buttons in the membership basis field for
each type). There should be no resigned
members on this list.
I see 2 physical things you are trying to track here: people and
actions.
Therefore, I would have two tables: people and actions. Or rather let
me say I would want two tables. As you build this, pounding it into
servitude with the screens and reports you want, you'll find yourself
compromising and doing what you need to do, within your ability, to
get the behavior you want. None of us end up with a solution as pure
and simple as we envisioned.
So anyway, starting out, I'd have the 2 tables. People will have a
unique serial type id field, along with first name, last, birthday,
possibly SSN, whatever. At this point you already have a decision to
make: does each person get a single address and phone number, or is
your system going to allow for home, work, 2nd home, cell, pager; in
other words: multiple locations and contact methods per member, (some
of which you won't be able to think of today). If the answer is the
latter, then you want separate tables for addresses and phones. Of
course those would be related to the people table via the unique id
field in people. It's fairly easy to have 3 phone fields (home, work,
cell), but it's more tedious to have multiple address fields. Your
call though.
The actions table will be related to people via people's unique serial
id again. You'll have a field in the actions table to hold that value.
In the Relationships tab of the Manage Database dialog, you'll expand
the two tables so you can see all the fields and drag a line from the
unique serial field in people to the foreign key field in actions. In
the actions table you'll have the foreign key field for the link back
to people that I just mentioned, and at a minimum also 2 more fields:
Action Date and Action Description. The description field holds all
the values you wanted to keep up with. Action Date of course, holds
the date; usually the date of entry, so an "auto-enter the current
date" option would work nicely here, but be sure the user can back or
post-date an activity if need be.
For the way I would use it, Double-click the equals sign on that
relationship line and enable allow creation of related records on the
actions side, and also sort that side of the relationship using Action
Date descending. That way the first related Action record returned to
the main people table will be the most recent activity. I'd have a
portal on the people screen for the actions. You can just start typing
in the blank portal row to create a new action. As you scroll down the
portal, you are looking back thru time at the member's historical
activity. The Action Descriptions would hold your values like
"Founded", "Invested", "Joined", Resigned", ... A portal might read
thusly:
9/1/2008 Resigned
7//2/2004 Vested
1/2/1999 Joined
11/15/1998 Contacted
When viewing or printing from the people table, if you throw the
Action::Action Description field directly on the layout, you will get
the value "Resigned" due to the descending sort. I can already see
that I would want a note field for each action, along with a user
name...
I could go on forever but hopefully that helps you some.
Geoff
