Using Access for Mail Merges - General Questions

[Stavrogin]

My question relates to how to organize (and update) a large mailing
list in Access 2002/2003.

I have an Excel mailing list of about 9,000 individuals, which is
broken into several subgroupings. Each subgrouping has received
different marketing material over the last three years and it is very
important to keep track of who has received what. Making things more
complex, some of the subgroupings overlap (i.e. one individual might be
part of 2 or 3 subgroupings).

I have now switched over to Access and find it to be very difficult to
update a database so large in size. At present, I have one single
"main" table that has 9,000 names and the corresponding addresses. I
now find myself trying to add the "correspondance" information - i.e.
who has received what and when. The only ways I can see of doing this
are:

1. Create a new "correspondance" table for the particular mailout and
select all 2,500 individuals using a lookup table. (I would want to
use the lookup so I could maintain a relationship between this table
and the main table).

2. Add a field in the main table that specifies that this particular
subgrouping has received this information. Here again, I would
seemingly have to add the new variable one person at a time.


Suffice to say, in both cases the updating simply takes too long.

So my questions are:

1. Is there any way to create a database where you can update... let's
say 2,500 names... at a single time?

2. Is there any way to practically separate the "correspondance"
information from the "main" information if we are dealing with a
database of substantial size? To clarify - is it possible to create
one table that just deals with the individual's address information and
another table that just deals with the individual's correspondances
(i.e. what they have received in the mail and when)?

Sorry if this is a little bit confusing... I'm relatively new to Access
and although I've read quite a bit on it, am still working through a
bit of haze on some topics.


Thanks

 

[KARL DEWEY]

Add a TEMP field. Use a query to display the fields necessary to make your
"main" & "correspondence" choice and the TEMP field. Set criteria for TEMP
field to Is Null (this way you can do it as time permits and not see those
you have done.

Place a letter in the TEMP field like M for main and C for correspondence.
You might add the use of another letter for other things like B for both.

Then you can run a make table query with the letter as criteria.

 

[Stavrogin]

I'm sorry if this sounds a bit ignorant... but what is a TEMP field?

Is TEMP a command or an actual variable... or are you just using it as
shorthand for "temporary"?

Bear with me... my entire Access knowledge comes from "Access 2003:
All-In-One Desk Reference for Dummies"


Thanks again

 

[KARL DEWEY]

You can call the field most anything. It is a flag field for you to label
the records as main or correspondence.

I suggest you make backup copies of your databases and just try things.

Build a little - Test a little.

 

[david epsom dot com dot au]

So my questions are:

1. Is there any way to create a database where you can update... let's

Type [ctrl][g]

in the window, type

codedb.execute "update mytable set maildate = date() where group = 1"

That would update the maildate to todays date for everyone in group 1.


that is called an "update" query. You can save your update
queries in the query window, then call them from a macro, or
from a switchboard, or from the query window.

2. Is there any way to practically separate the "correspondance"
information from the "main" information if we are dealing with a

Yes, create a new table for the correspondance information.

seemingly have to add the new variable one person at a time.

If there is a way you can select the people as a group - by
a category field, or a last mail date, or by company, postcode
or alphabet - then you can update that group with a single
command. But you have to write the command, or build it in
the query design window.

(david)

 

[Albert D.Kallal]

I have an Excel mailing list of about 9,000 individuals

Just so you know, a file size of 9,000 records is considered VERY small. In
fact, if you have 100,000 records, ms-access can sort, and producing
groupings in WELL UNDER one second. So, just to be clear, you go a small
file..and even 10 x that size will not even slow down ms-access in any
noticeable way.

, which is
broken into several subgroupings.

How are the sub-groupsing made?

It sounds like you need a table that manages, and can give a list of these
groupings. Perhaps date of grouping, a nice text description etc.

Lets call this table MGroupings

Each subgrouping has received
different marketing material over the last three years and it is very
important to keep track of who has received what

Hum, lets create another table, and lets call it

GroupsMaterialRecicved.

Making things more
complex, some of the subgroupings overlap (i.e. one individual might be
part of 2 or 3 subgroupings).

Ok, the above means we need another table. Lets called.

GroupingsThatAIndivialReceived.
or, perhaps better name would be
GroupsThatAPerssonBelongsTo

1. Create a new "correspondance" table for the particular mailout and
select all 2,500 individuals using a lookup table.

What do you mean only got 2500 individuals? I thought we had 9000 people
here?

Remember, in a proper database, you only have ONE copy of the person, and
their address. You don't copy the names over and over (that is too much
work, and too wasteful of both people time.

Anyway, lets assume we have 2500 people that belong to the "CoolGuysGroup".
If the database is setup correctly, then ......

2. Add a field in the main table that specifies that this particular
subgrouping has received this information. Here again, I would
seemingly have to add the new variable one person at a time.

No, if fact, lets assume that you have the ONE record in the MGroupings
table.

You then attach ONE record of the table GroupsMaterialRecicved.
By adding ONE record to the above group, then 200, or the 2500 in your case
will INSTANTLY be updated (in fact, they are not updated, but by means of
relational database workings, they all will now show they received some
material. This is the magic of a relational database. As adding one record,
will instantly show for all names.

In other words, if you learn how to do this in a relational database, you
will find managing of such a small file child's play...

1. Is there any way to create a database where you can update... let's
say 2,500 names... at a single time?

As mentioned, if those names are attached to particular group, then adding
ONE record to that group will show for ALL names attached to that group.
That means you don't have to update 2500 records (you would if you are
adding ALL of them to a new group).

2. Is there any way to practically separate the "correspondance"
information from the "main" information if we are dealing with a
database of substantial size? To clarify - is it possible to create
one table that just deals with the individual's address information and
another table that just deals with the individual's correspondances
(i.e. what they have received in the mail and when)?

Yes, the above is on the right track. How far you "normalize" the data is up
to you (the process of breaking down repeating data in to new tables so you
only have to add ONE record to update the whole mess is called normalizing,
and is the MOST important skill you can learn).

Here is some reading on normalizing:

ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Data Normalization Fundamentals by Luke Chung
http://www.fmsinc.com/tpapers/datanorm/index.html

324613 - Support WebCast: Database Normalization Basics
http://support.microsoft.com/?id=324613

 

[Stavrogin]

Thanks for the responses.

To Albert... I am trying to work through the problem over the weekend
using your instructions. I will let you know if I was able to do it
correctly in a day or so.

Again, thanks very much for the help.

 

[Stavrogin]

I've run into two problems with the instructions you suggested. I was
hoping you could help me out a bit more.

I believe I understand the significance of the tables you suggested I
create (but please correct me if I'm wrong). "MGroupings" is to list
the different groups I have created and provide a bit of detail about
that group. "GroupsMaterialRecieved" is to list what materials each
group received. "GroupsThatAPersonBelongsTo" is to list what groups a
particular person belongs (in other words, what persons belong to each
group).

The two problems:

1. To create that third table, how would I specify what groups an
individual person belongs to without having to do it one name at a
time? In other words, if "John Jones" belongs to Group A & Group B and
"Susan Steel" belongs to Group C & Group D, how do I specify that they
belong to this group without having to individual change their records
in the main table?

2. You wrote that if "those names are attached to a particular group,
then adding one record to that group will show for ALL names attached
to that group." I am not clear what attached means in this context.
How do I attach 2,500 names (out of a table of 9,000 names) to one
particular group? I should say upfront I do not have a good grasp on
relationships and I don't know what they do (if anything) beyond
enabling a lookup field. Because I have this view of relationships, I
can only envision a situation where I am using a drop down box of 9,000
names to select one person as belonging to the "CoolGuysGroup."
Evidently, this is not what you're saying.

Anyway, sorry to bother you again. And thanks very much for the
links... they're helping me understand this better.

 

[Albert D.Kallal]

"MGroupings" is to list
the different groups I have created and provide a bit of detail about
that group.

yes, above sounds good.

"GroupsMaterialRecieved" is to list what materials each
group received.

yes, above sounds good.


"GroupsThatAPersonBelongsTo" is to list what groups a

particular person belongs (in other words, what persons belong to each
group).

yes, above sounds good.

In other words, if "John Jones" belongs to Group A & Group B and
"Susan Steel" belongs to Group C & Group D, how do I specify that they
belong to this group without having to individual change their records
in the main table?

You don't change the main table in this case, you add a record to the
GroupsThatAPersonBelongsTo table.

You don't mention how you know now that Jones belongs group a? (how do you
know this now?). Where "was" this information?

There is no question that you have to add each individual to a group
(GroupsThatAPersonBelongsTo). (this can be done by some select query, or
when you add the person, or simply one by one).

To add Jones to groupA, then you would simply add ONE record to
GroupsThatAPersonBelongsTo

So, you table GroupsThatAPersonBelongsTo would only need two columns, and
might look like


Customer_id Mgroupings_id

(note that for all my tables, I assume a Primary key (PK) of id, and for
foreign keys (fields used to relate from child tables to master tables, I
use the tablename_ID). (you can use any type of naming scheme that works for
you).


So, there is no question that you somehow have to join a person to groupA,
but how the heck did you do that before? If before you added a new field
each time, then your code, data input screens, reports, and all kinds of
stuff will have to be CHANGED for each new field you add (adding fields to a
design is VERY expensive, as forms, code, reports, queries etc. now needs to
be modified. Can you imagine if for each new inventory type in a accounting
package you have to call in the developers?..or modify 100's of screens and
lines of code?).

If you got a mess now where you added new fields for each grouping to the
main customer file, then you need to fix this mess. You will need to run a
series of append queries that grabs the group, and the customer ID and adds
it to the GroupsThatAPersonBelongsTo table. (you can run queries on existing
data to build these records). Once you do this, then you can remove the
fields that exist in the main customer record that shows what group(S) they
belong to (and, we would be working a copy of the data of course!!).
Further, we are assuming NO duplicates of customers in the main file (at
least not on purpose).

So, yes, for the initial setup, you will have to run a series of append
queries to build that GroupsThatAPersonBelongsTo table. (as mentioned, how
did the groups get tracked before?).

2. You wrote that if "those names are attached to a particular group,
then adding one record to that group will show for ALL names attached
to that group." I am not clear what attached means in this context.
How do I attach 2,500 names (out of a table of 9,000 names) to one
particular group?

See, above explain. What I saying is if you update materials sent to a
particular group, then all members of the group will show this information.

As I mentioned, how do you know a person belongs to group A now? How did
that happen?

I should say upfront I do not have a good grasp on
relationships and I don't know what they do (if anything) beyond
enabling a lookup field. Because I have this view of relationships, I
can only envision a situation where I am using a drop down box of 9,000
names to select one person as belonging to the "CoolGuysGroup."
Evidently, this is not what you're saying

No, you no doubt have a VERY good means to search for, and find a
individual. AFTER you found that individual, then you will display
information such as what groups etc. the person belongs to. You can get some
ideas for searching here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

So, lets say we got 20 groups. A list of 20 options in a combo box is VERY
easy to build, and VERY easy to deal with.

When you build a screen to produce mailing labels for CoolGuysGroup, that
combo box will be filled by our now VERY convenient table called MGroupings
(and, even better, you can include a group name, and group description in
that combo..so it is more user friendly). So, each of these tables tends to
make the whole application easier to deal with (for example, can you print
out a list of groupings you have?....with our MGroupings table..it sure is
easy).

Remember, if you got 2000 people in groupA, and you add information sent to
group a, you will be adding only ONE record to the database
(GroupsMaterialRecieved)

However, what about adding individuals to a particular grouping?

Lets assume we want to add Mr. John Goofy to the GroupA
Lets also assume you have a VERY nice means to search, find, and display
that one customer called Goofy.

You would build a sub-form in the customer file (perhaps behind a tab called
Customer groupings). This would simply be the main form customers, and the
sub-form based on the GroupsThatAPersonBelongsTo table. The sub-form will
automatically enter the customer_id field for you (if you set the link
master + link child fields). The only other field we need to set is the
Mgroupings_id. We could type this in manually, but who the heck is going to
know the id of each of the Mgroupings? So, what you do is simply make that
2nd column of the sub-form (based on GroupsThatAPersonBelongsTo ) a nice
combo box that is based on the MGroupings table. So, what you will get a
continues sub-form and for each row you will have a single combo box to
select what groups a person belongs to. For each new group, you would add a
new entry in this sub-form.

Sub-forms are great fun to make. Take at the following sub-forms, and you
can see that MANY have a combo box that eliminates the need for users to
type in some id they don't know, nor can remember (so, make the combo box
displays the nice grouping name..but stores the ID of Mgroups into field
Mgroupings_id).

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

 

[Stavrogin]

Hi Albert:

Thank you for the lengthy response. I'm sorry I did not get back to
you sooner but there was a personal situation that occupied most my
time.

Anyway, I think I now understand how the tables are to work. If I am
not mistaken, the table "GroupsAPersonBelongsTo" is a junction table
because several customers can belong to a single group and several
groups could include a single customer. The table should have 3 fields
(the 2 you listed) plus a third field that is the primary key. If that
is accurate then I think I have a handle on things.

Before I go, I have one more quick question - say, instead of
categorizing people by grouping, I categorize them by mailout. I would
want to do this so I could easily keep track of who responded to each
particular mailout (rather than keeping track of which group most of
the responses come from). The way I can conceive of doing this is by
using your proposed structure, but replacing the table
"GroupsAPersonBelongsTo" with a new table called
"MailoutsAPersonBelongsTo".

My concern/question in this regard is let's say I do 5 mailouts a year
to 10,000 people. By the end of the year, I'll have 50,000 entries in
one table. By the end of two years, I'll have 100,000 entries. Is it
reasonable to allow a table to grow to that size or once it approaches
the 100,000 figure, do I start to experience significant slowdown?

Anyway, thanks for all the help and I'm sorry I haven't been more
prompt in responding... but there have been some difficult situations
on the personal end that have occupied my time.