Saving information in TWO tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My agency has decided to create a mass mailing database by gathering contact
information from 6 of our other databases. I'm assuming the best way to do
this is through AfterUpdate events, Update queries, and Append queries. My
question is: Do I link the Contact Info table from the Mass Mailing database
into each of the 6 other databases and update the table everytime information
is added to those databases, or do I link each of the Contact Information
tables from the 6 other databases into the Mass Mailing database and update
the table only when this database is opened. Does it matter?
 
Here is a thought for you. Put all the information in a single table in a
database available to all. Add fields the signify what the entry is --
vendor, customer, regulatory agency, utiity company (water, electric, phone),
etc.

Then everyone queries the table with criteria for their particuliar interest
list.
 
Well, what I'm really looking for is one main place that is accessed only by
me and our PR person. Each database already has a system in place to create
their own mailing list if need be, although that rarely happens. At most,
someone may need to send a letter to one or two people in their programs once
a month. Each program is independent of the others, so there's really no
need to have the Mass Mailing database accessible to everyone. Thanks for
the input though.
 
My agency has decided to create a mass mailing database by gathering contact
information from 6 of our other databases. I'm assuming the best way to do
this is through AfterUpdate events, Update queries, and Append queries. My
question is: Do I link the Contact Info table from the Mass Mailing database
into each of the 6 other databases and update the table everytime information
is added to those databases, or do I link each of the Contact Information
tables from the 6 other databases into the Mass Mailing database and update
the table only when this database is opened. Does it matter?
Click to expand...

Karl's suggestion is a good one, and you should seriously consider it.
Having six different tables to store basically the same type of data
is not good design, and is not necessary. What about overlap? Might
you not have the same person turning up - perhaps with the name
spelled a bit differently, or at "Maple St." instead of "Maple
Street", in two tables? Shouldn't the data be stored consistantly?

That said... you may want to create a UNION query to dynamically
splice together the six tables just for the purpose of your mass
mailing report. Storing the data redundantly in yet a seventh table is
the *least* attractive option IMO.

John W. Vinson[MVP]
 
While I understand the concerns with having the same data in so many tables,
it really is the only option for us, for several reasons, at least for right
now. The idea of one database is interesting though and one I might consider
in the future.

I hadn't considered a union query, so I think for now that's the route I
might take. Thanks for the feedback.
 
Hi Tara,

It might be useful to explain what those several detabases are and why
eeach seems to be able to do the same thing. Are they several copies
of the same database application? If the databases provide a common
function the usual topology is to have a Front End on each user's
desktop and the common Back End on a server or network share available
to all. Other issues arise in getting from here to there but it
avoids situations such as the one you face now.

If a change of topography isn't in the offing then I suggest that you
create a separate application that will interrogate each of the
several databases for the applicable data, massage the data and add
the new data to the new database. Assuming that future actions will
be somewhat predicated on past actions, you'd want to keep the data in
the new application indefinitely. The huge caveat being that it is
necessary to bring the new database up to date before the email run.

HTH
 
Thanks for the feedback Larry. We are a social service agency that offers
several different programs, such as Early Intervention, a Fathering program,
a Program called Healthy Families, and a program called HOME which works
closely with the DFC's in our area. We also offer an Early Literacy program
and a WIC program as well as a Prenatal Program. The programs are quite
different from one another and the data each works with is unique to each
program. The ONLY thing they have in common as far as the data they collect
and how it is collected are Names and Addresses. Creating one large database
just won't work, at least right now. I do like the idea of using a more
integrated system and using queries to access data, forms, etc. relevant to
whatever program the user is trying to access at the time, but right now,
that idea is much too time-consuming of an endeavor. Simply compiling all of
the names and addresses of those we serve into one place, and being able to
keep it updated easily, is our goal right now. A separate database is our
best option for the time-being.
 
Hi Tara,

It sounds like you're forever going to be buried in what's going on so
you'll never have the time available to make your tools better. As
you may gather from the responses you've received, there are many good
reasons for doing it differently.

I've just erased as much as I had written toward getting you going
with a hybrid application that might help. Pretty well through it I
realized that anything short of doing it right would be just about as
much work as doing it right but would be very risky.

The reason it's so risky is that the names of your clients and their
personal data are the records to which al their other data is related.
The mailing list issue is symptomatic of what's wrong with your
current operations. You should really have all of that information
available to you so that you can not only manage mailing lists, you
could also get up-to-the-minute counts of your client base and list
the numbers of events and services completed or on-going in all
categories with applicable dates, etc. You can't efficiently and
effectively do that now because the data is kept separately by
different people and lots of things get lost in the cracks of personal
interpretations of what should be going on.

Good luck with it.
 
Back
Top