How do delete queries work with tables that are related?

[Guest]

I have "master" table that is related to 6 other tables by email address,
name, address, newsletter subscription and specific areas of interest (areas
of interest and newsletter subscription are yes/no fields) All of the tables
have "record ID" (auto-number) as the primary key. The 6 tables are distinct
areas of interest to the customer. The master table is a combination of all
interests of the customer plus a newsletter subscription field. I receive
the data via web forms that are mapped to the separate tables in the
database. (there are many places a person can sign up for different types
information on the web site). The information is stored in the specific table
that is mapped to the sign up form on the webpage (one person's information
can be located in many tables), but is also added to the master table if a
"yes" box is checked for the newsletter subscribtion on the web form. The
master table is then used as my datasource to run newsletter marketing
campaigns. The other tables are used marketing for other specifc areas of
interests. When I run a newsletter campaign, the recipient can "unsubscribe"
to the newsletter. If unsubscribed, the master datatable field "unsubscribe"
is incremented to a "1". I can then run a delete query where
"unsubscribe"=>0. This part works fine. My problem is that if someone signs
up for one area of interest and checks the newsletter subscription, then
subsequently wants to unsubscribe to the newsletter but keep his information
in the original area of interest table, will running the "unsubscribe" query
in the master delete all of the records in the other tables because they are
related? Would a better option be to remove the relationships and then append
the records that want the newletter(by checking yes on the webform) to the
master table then if the person later unsubscribes he will be deleted from
only the master table?

 

[Jeff Boyce]

Kristine

A couple things to consider...

If you meant, literally, that each table uses an Access Autonumber as a
RecordID, then there is NO relationship among the tables' Autonumber/Primary
Keys, since Access generates each one independently. If you are using a
foreign key in the "6 tables" to hold a copy of your master table's RecordID
(and those fields are long integers), then that's different.

It sounds like you are copying all of the information that's in each of the
"6" into your master -- there are many reasons why this is a bad idea in a
relational database, but the effort you will need to make to keep all that
synchronized is a strong reason why YOU don't want to do that.

Rather than have folks re-enter all their information for each subscription
(if I'm understanding your arrangement), you could have a master set of
info, an "available subscriptions" table (one row per subscription), and a
"subscribers" table that has one row for each valid combination of person
and subscription.

With a design like this, you WON'T have to add another table (to your "6")
if you wanted to add another subscription type ... you'd just add it to the
"subscriptions" table.

Or have I misinterpreted your design?

Regards

Jeff Boyce
Microsoft Office/Access MVP