One-to-One/One-to-Many relationship

[yamefui]

Hello, my issue is trying to obtain one to many relationships for the
following tables. I have listed just a few of the fields (minus the naming
conventions) for each proposed table. I believe I made the proper decision in
separating the tables this way as to avoid data duplication if I had only
used one main table.

The difference is that the contacts/individuals in table 1 will almost
always have 3-4 (or more!) different addresses (business, home, alternate,
mailing) and phone numbers (cell, home, business, alternate, fax, etc.)
instead of just one.

What method can I use to create one-to-many relationships between table 1
and tables 2 and 3? If I create the Autonumber field ContactID and name it as
the Primary key in each table, for example, and develop relationships in the
Relationship window, I can only get one-to-one connections which I don't
want.

I'm a bit confused on the primary-foreign key concept, even after
reading/using help. Suggestions are very welcome. Thank you.

Fields in table 1 (tblContactInformation):
FirstName
MiddleName
LastName
Company
Website
Title
BusinessCategory

Fields in table 2 (tblAddressInformation):
StreetAddress
City
State
ZipCode

Fields in table 3 (tblPhoneInformation):
BusinessPhone
MobilePhone
CellPhone
Fax

 

[Douglas J. Steele]

While ContactID may be the primary key in tblContactInformation, it cannot
be the primary key in the other two tables if you want a one-to-many
relationship.

For the Addresses, you need to add an additional field AddressType (is it
home address, work address, mailing address, etc.). The primary key would
then be the combination of ContactID and AddressType. Note that ContactID
would be a Long Integer field in this table, not an Autonumber field: you
need to be able to control what value is used for ContactID as you insert
the various records.

Your Phone table, as it stands, shouldn't be a one-to-many: it's already
denormalized, so that a one-to-one relationship is all you need. If you want
to be able to handle, say, multiple cell phone numbers, then you need
something like:

Fields in table 3 (tblPhoneInformation):
ContactID (Long Integer)
PhoneType
PhoneNumber

and the primary key would then be the combination of ContactID and
PhoneType.