Mailing Lists filters

[Tom]

** I am new to Access**

But I am willing to learn. Here's my question; I am moving a file that
I have used successfully in Excel for several years to track donations
to a scholarship fund. I have approx. 300 names in the file, however
over the years people have stopped donating or died, whatever.

My question is that when I go to do the next mailing, I will decide
who I will be including. How do i cull out this group from the 300? In
Excel I would simply copy the entire list, then filter out those I
didn't want and save those remaining on the list as a new file.

Thanks for your help.

 

[Ken Sheridan]

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

 

[Arvin Meyer [MVP]]

It's even easier in Access. Add a yes/no column to choose which names you
want to include. For your list, simply build a query and add the word "yes"
to the new column's criteria.

 

[fredg]

** I am new to Access**

But I am willing to learn. Here's my question; I am moving a file that
I have used successfully in Excel for several years to track donations
to a scholarship fund. I have approx. 300 names in the file, however
over the years people have stopped donating or died, whatever.

My question is that when I go to do the next mailing, I will decide
who I will be including. How do i cull out this group from the 300? In
Excel I would simply copy the entire list, then filter out those I
didn't want and save those remaining on the list as a new file.

Thanks for your help.

I assume you wish the donation letter to be personalized, rather than
just a label to stick on the envelope.

One way...
Import the entire Excel file into an Access table.
Then open the table in Design view.
Add a new field:
Name it 'Active'
Check box YesNo datatype

Add a new DonorID field.
AutoNumber Indexed No Duplicates
Make this field the Prime Key field.

Create a form, based using this table as it's record source.
Include all of the fields in the form.
Set it's Default View to Continuous

Now open the form.
Navigate through all of the records, checking the Active check box for
each person who is still actively donating.

Create a new query.
Include all of the fields of this table.
As criteria on this query's 'Active' field write:
Yes
Name this query qryActiveDonors

Create a new letter report asking for a donation.
Set the report's record source to the query.
If you need help with the personalized letter, post back with more
information.

When you wish to mail requests for a donation the donor names will
have been filtered to just the active ones.

Is this what you want?

 

[Tom]

It's even easier in Access. Add a yes/no column to choose which names you
want to include. For your list, simply build a query and add the word "yes"
to the new column's criteria.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com









- Show quoted text -

Ok, if I add the yes/no and run a query..I get that. But doesn't a
mail merge have to be done against a table?

 

[Tom]

I assume you wish the donation letter to be personalized, rather than
just a label to stick on the envelope.

One way...
Import the entire Excel file into an Access table.
Then open the table in Design view.
Add a new field:
Name it 'Active'
Check box  YesNo datatype

Add a new DonorID field.
AutoNumber Indexed No Duplicates
Make this field the Prime Key field.

Create a form, based using this table as it's record source.
Include all of the fields in the form.
Set it's Default View to Continuous

Now open the form.
Navigate through all of the records, checking the Active check box for
each person who is still actively donating.

Create a new query.
Include all of the fields of this table.
As criteria on this query's  'Active' field write:
Yes
Name this query qryActiveDonors

Create a new letter report asking for a donation.
Set the report's record source to the query.
If you need help with the personalized letter, post back with more
information.

When you wish to mail requests for a donation the donor names will
have been filtered to just the active ones.

Is this what you want?

The mail merge letter is part of what I want, but I will also need to
make the mailing labels. I think I am reading in your response that
the "mail merge" can be done from a query result. Is this correct?
What about the mailing labels?

 

[John W. Vinson]

Ok, if I add the yes/no and run a query..I get that. But doesn't a
mail merge have to be done against a table?

No. You can do anything with a Query that you can do with a Table. Queries are
the backbone of any Access app.

 

[fredg]

The mail merge letter is part of what I want, but I will also need to
make the mailing labels. I think I am reading in your response that
the "mail merge" can be done from a query result. Is this correct?
What about the mailing labels?

Yes, you would use the query as the basis for the mail merge and also
as the basis for the mailing labels.
The table holds all of the donors names, active and inactive.
This way, if an inactive donor becomes active again, all you need do
is check the check box.
The query will display only the active ones.

 

[Ken Sheridan]

You can download a demo showing how to undertake various Access to Word
operations, including mail merging, inserting data into bookmarks etc at:


http://community.netscape.com/n/pfx/forum.aspx?msg=23781.1&nav=messages&webtag=ws-msdevapps


The demo uses a query as the basis of the merge.

Ken Sheridan
Stafford, England

 

[elizabeth]

hey can we please send messages