Mailing List Maintenance DB

[jason]

Trying to create a DB that will help me manage/maintain mailing lists
(regular mail, NOT email).
Here's how I have it layed out so far:
Mail_List_tbl
NameID (autonumber)
Address
City
State
Zip

Mail_Info_tbl
MailerID
MailerDescription
Mailer_Date

Mailer_Join_tbl
NameID
MailerID

I imagine I have to have a One to Many relationship from
Mailer_Join_tbl.NameID to Mail_List_tbl.NameID and another One to Many from
Mailer_Join_tbl.MailerID to Mail_Info_tbl.MailerID.
We could potentially use a list name more than once in different mailers.

Couple of things I'm not sure about:
How we can mark a name with a corresponding MailerID. If we use Names
100-200, how do we automatically indicate that those names were used with
mailer "2006Postcard," for example? Through a query?

I started this little ditty in Access and so far seems to make sense. The
only thing that makes me think that I messed something up is when I try to
query names that belong to two different mailers (i.e., Jon Smith was used
in 2005PCMailer and 2006PCMailer), in which case, no list names are shown in
the results although there should be.

By having the DB setup this way will anything be affected if we later delete
names from the list. We may try to compare names of current patients to
names on the list. If the names/addresses match, those names may be deleted
from the mailing list.

Any help, suggestions would be appreciated.
Thanks.

 

[Smartin]

Please see inline

Trying to create a DB that will help me manage/maintain mailing lists
(regular mail, NOT email).
Here's how I have it layed out so far:
Mail_List_tbl
NameID (autonumber)
Address
City
State
Zip

Mail_Info_tbl
MailerID
MailerDescription
Mailer_Date

Mailer_Join_tbl
NameID
MailerID

I imagine I have to have a One to Many relationship from
Mailer_Join_tbl.NameID to Mail_List_tbl.NameID and another One to Many from
Mailer_Join_tbl.MailerID to Mail_Info_tbl.MailerID.
We could potentially use a list name more than once in different mailers.

Couple of things I'm not sure about:
How we can mark a name with a corresponding MailerID. If we use Names
100-200, how do we automatically indicate that those names were used with
mailer "2006Postcard," for example? Through a query?

Your Mailer_Join_tbl connects names and mailers.

If you want to know which names received which mailers all you need to
know is the MailerID fitting the description "2006Postcard". Then join
the name from Mail_List_tbl.

I started this little ditty in Access and so far seems to make sense. The
only thing that makes me think that I messed something up is when I try to
query names that belong to two different mailers (i.e., Jon Smith was used
in 2005PCMailer and 2006PCMailer), in which case, no list names are shown in
the results although there should be.

Offhand I'm not sure. Let's see the SQL?

By having the DB setup this way will anything be affected if we later delete
names from the list. We may try to compare names of current patients to
names on the list. If the names/addresses match, those names may be deleted
from the mailing list.

If you delete from Mail_List_tbl you will still have entries in the
other two tables, assuming you do not have cascade delete set up with
the relationship. Such is the beauty of relational design....

Any help, suggestions would be appreciated.
Thanks.

HTH

 

[jason]

Thanks for the tip.

Please see inline


Your Mailer_Join_tbl connects names and mailers.

If you want to know which names received which mailers all you need to
know is the MailerID fitting the description "2006Postcard". Then join the
name from Mail_List_tbl.


Offhand I'm not sure. Let's see the SQL?


If you delete from Mail_List_tbl you will still have entries in the other
two tables, assuming you do not have cascade delete set up with the
relationship. Such is the beauty of relational design....


HTH