Relationship/Query Problem

  • Thread starter Thread starter Kelly
  • Start date Start date
K

Kelly

I am building a database with the following design:

Table 1: Company info (e.g.: adress, main phone, etc.)
Table 2: Contacts (datasheet to list multiple contact
names, extensions, etc.)
Table 3: Mailings (specific categories for each contact
(e.g.: newsletter, calendar, etc.--all yes/no)

At the moment, I'm having a query dilemma. If I have
Joe, Jane and John all at ABC Company, with only John to
receive the Calendar...all three contacts come up as
output for the Calendar-Yes query. It's as if the Org ID
is linked to the Calendar instead of the Contact ID.

Any thoughts as to how the relationship should look?
 
I did that same setup not long ago. You need

tblOrgs
OrgID PK
etc

tblContacts
ContactID PK
OrgID FK
etc

tblMailouts
MailID PK
ContactID FK
etc

So you relate many Contacts to one Organisation, and many Mailouts to one
Contact

In fact in my case I had to allow many Organisations to many Contacts, with
a junction table to relate them, but still relate the Mailouts to the
Contacts.
--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Adrian,
I think I have this done...yet the query is still not
working. Now none of the contacts come out in the
output. I feel like I'm banging my head against a wall.
If I zipped the file, would you mind having a glance over
it?
Many thanks for the reply,
 
Kelly,

So far, you haven't really given the information that anyone would need
in order to offer specific advice. We would need to know the fields in
the tables in more detail than you have given, and the details of the
query (posting the SQL view of the query would be ideal).

As hinted at by Adrian, the mailing categories as Yes/No fields is a
poor design, but it is not clear from your post whether you have
corrected this.

I would recommend against getting someone to solve it for you. First of
all, you deny the benefit to others watching this thread. And for
yourself, you will learn a lot more by working through it within the
newsgroup.
 
Just post your table key field list here, also the SQL of the queries on
which the forms are based, and the master/child links on the forms you are
using.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Below should give a better idea as to where I stand at
the moment:

Main Table: CompanyID, Company Name, Address, Main Phone
Contacts Table (datasheet form): ContactID, CompanyID
Last Name, First Name, E-mail
Mailings Table (datasheet form): MailingID, CompanyID,
ContactID, Salutation, then checkfields for mailing
categories such as Calendar, Newsletter

I have a one-to-many relationship with the following:
CompanyID from Main Table to Contacts Table; ContactID
from Contacts Table to Mailings Table

I get an error message when I try to enter in anything in
the Mailngs Table "related record needed in Contact
Table". PLEASE HELP!!!!!!!!
 
Kelly,

Thanks for the further clarification.

First point is that the Mailings table should not have a CompanyID field
in it at all. Just delete it. The mailings are related to the Contact,
not to the Company. Since the Contacts are related to the Company, you
can always derive which Company a mailing is going to.

Second point... what is Salutation? If this means what you are going to
call the Contact by, I think it should be in the Contacts table, not the
Mailings table.

Third point, possibly the most important... You should have each
mailing category for each Contact as a separate *record* in the Mailings
table, not as a separate *field*. If you were going to identify the
mailings for a contact with Yes/No fields, one for each mailing type,
then this could go directly in the Contacts table, and it would not be
applicable to have a Mailings table at all... but I only mention this
for academic interest, it is not a good design. Instead, your Mailings
table should have these fields:
MailingID
ContactID
MailingCategory

We can look at the design of your forms after you've got the tables sorted.
 
Back
Top