Creating relationships

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Senator - 03 Jul 2005 04:26 GMT

I have created a table of prospective customer records with an auto-generated
primary key. I also created a table for the purpose of maintaining detailed
information on mailings to selected prospects. The selected prospects are
retrieved via a query on the table of contacts using criteria. However, not
every query will result in a mailing.

Once I have created the query I ultimately want for the mailing, I’d like to
create a relationship between the contacts selected in the query and the
specific mailing. But I am unsure as to how to do it. Please note that not
every query will result in a mailing, there will be multiple mailings and any
given prospect could receive multiple mailings. Oh, and one more thing, when
I do subsequent queries, I’d like to see whether I have mailed anything to
them previously. Any ideas?

Many thanks.

Reply to this Message

Jeff Boyce - 03 Jul 2005 15:15 GMT

Given that Access is a relational database, in your situation, one table is
not enough!

It sounds like:
one prospect could have zero-to-many mailings, and
a specific mailing could have zero-to-many prospects to whom it was
mailed, and
you have prospects, and
you have specific mailings.

This sounds like three tables:

tblProspect
tblSpecificMailing
trelProspectMailing

That third table "resolves" the many-to-many relationship between prospects
and specific mailings.

That third table is the way you'd determine which prospects have received
which mailings.

Signature

Good luck

Jeff Boyce
<Access MVP>

> I have created a table of prospective customer records with an auto-generated
> primary key. I also created a table for the purpose of maintaining detailed
[quoted text clipped - 11 lines]
>
> Many thanks.

Reply to this Message

Senator - 03 Jul 2005 15:40 GMT

Yes, I think I'm with you. I have the two tables, one for prospects and the
other for mailings. I actually created a third table that contains only
"prospect ID" and "mailer ID". Is this what you were suggesting? I'm not
familiar enough with abbreviations or nomenclature. Is "trelProspectMailing"
something other than a table?

Then, assuming I run multiple queries, how do I link the specific query ( a
selection of prospects) to the specific mailing? I tried creating an "Append
query", but I could never get it to work and besides, I didn't want to create
a new record every time I ran a query. Thanks.

> Given that Access is a relational database, in your situation, one table is
> not enough!
[quoted text clipped - 39 lines]
> >
> > Many thanks.

Reply to this Message