In your Donors table add a new column (field), Status say, of text data type
in which you can insert values such as Active, Inactive, Deceased etc. Then
for your mailing list report base it on a query which has the criterion
'Active' for the Status column. Only the donors recorded as Active will be
included in the report.
However, there is another step you should take and that's create a new table
called Statuses with a single column Status, defined as its primary key, in
which you enter each of the terms Active, Inactive, Deceased etc as separate
rows (records). In the database's Relationships window relate this table to
your Donors table on the Status columns and enforce referential integrity and
cascade updates (but not cascade deletes). This will ensure that you can
only have valid Status values in the Donors table (guarding against typos
etc), and that if you change a Status value in the Statuses table for any
reason, e.g. from Deceased to Dead, then all the matching values in the
Donors table will automatically change. BTW 'value' in database terms has
nothing to do with 'worth', its just an instance of an attribute type, e.g.
Active and Inactive are values of the Status attribute type. Columns in a
table represent attribute types for the entity type which the table
represents.
Having a separate Statuses table also makes data entry in the Donors table
easier because in you data entry form (data should always be entered via
forms, never in raw datasheet view of a table) you can have a combo box bound
to the Status column, with a RowSource of:
SELECT Status FROM Statuses ORDER BY Status;
so the user simply selects a status value from the list rather than having
to type it in.
As you’ve, been storing your data in Excel its likely its not currently in
the best form for storage in a relational database like Access. It will
probably contain 'redundancy', i.e. the same 'fact' will be repeated more
than once. In a relational database redundancy is undesirable because it
allows inconsistent data to be entered, and is eliminated by a process known
as normalization, which involves decomposing a table into several related
tables. While I can't say without more information what might be required in
you case here's a brief outline of the principles of normalization which
hopefully give you an idea of what's involved:
"Normalization is the process of eliminating redundancy from a database, and
involves decomposing a table into several related tables. In a relational
database each table represents an entity type, e.g. Contacts, Companies,
Cities, States etc. and each column in a table represents an attribute of the
entity type, e.g. ContactID, FirstName and LastName might be attributes of
Contacts and hence columns of a Contacts table. Its important that each
attribute must be specific to the entity type, so that each 'fact' is stored
once only. In the jargon its said that the attribute is 'functionally
dependent' solely on the whole of the primary key of a table.
To relate tables a 'referencing' table will have a foreign key column which
makes the link to the 'referenced' table, e.g. a Contacts table might have a
CompanyID column as a foreign key, while a Companies table has a CompanyID
column as its primary key. Consequently no data other than the CompanyID
needs to be stored in a row in the Contacts table for all the company
information for that contact to be known; its available via the relationship
and can be returned in a query joining the two tables on the CompanyID
columns.
Similarly the Companies table might have a CityID column and the Cities
table a StateID column. If its an international database the States (or more
generically Regions) table would have a CountryID referencing the primary key
of a Countries table. So via the relationships, simply by entering (in
reality this would be selected from a list of Companies in a combo box, not
typed in) a CompanyID in the Contacts table the location of the contact's
company is also known. Redundancy, and therefore repetitive data entry is
avoided.
To see how a database is made up of related tables take a look at the sample
Northwind database. Its not completely normalized in fact (deliberately so
for the sake of simplicity) but illustrates the main principles of how tables
representing entity types relate to each other. An example of its lack of
proper normalization can be found in its Customers table. You'll see that
this has City, Region and Country columns so we are told numerous times that
São Paulo is in SP region (as is Resende) and that SP region is in Brazil.
Not only does this require repetitive data entry, but more importantly it
opens up the risk of 'update anomalies', e.g. it would be perfectly possible
to put São Paulo in California in one row and California in Ireland! Proper
normalization as I described above would prevent this as the fact that São
Paulo is in SP region would be stored only once in the database as would the
fact that SP region is in Brazil and California is in the USA."
As you've been recording donations over a number of years you'll no doubt
have a number of different donations per donor recorded, so you'll most
probably need a Donors table and a Donations table, the latter with a foreign
key DonorID column, referencing the primary key of Donors, and columns such
as Amount and DonationDate (don't use date as a column name; it’s the name of
a built in function so should be avoided). You might also have a column in
the Donations table as a foreign key referencing another table if your fund
is subdivided, so that the subdivision of your fund to which each donation
relates is recorded. In this scenario the Donations table would actually be
modelling a many-to-many relationship type between donors and the
subdivisions of your fund. However, I'm just guessing here and would need
more information to advise in detail as to what the appropriate 'logical
model' for your database might be.
Ken Sheridan
Stafford, England
