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

 

[Guest]

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

 

[Guest]

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.

 

[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.