Tables with multiple relationships

[computergirl]

I am new to working with relationships and could use help figuring out how to properly connect these two tables. I have a Company Table and an Contact table that have both a one-to-one and one to many relationship. The One-to-many relationship is the one company to many contacts and the one-to-one is the primary contact in the company table has at most one match in the contact table. I was sure to not require either field but when I try to add a new company it won't let me as the contact does not exist and when I try to add a new contact it won't let me as the company does not exist. This is for a class and they are on spring break so I am not able to get help from my instructor until next week. Below was one of my table designs of the two table although I have removed all the lookup wizards and still no luck. Just to clarify, the one to one relationship is between the PrimaryContactID in the Agency table and the ContactID in the AgencyContact table. I also thought about trying an entity subtype for the PrimaryContactID, but wasn't sure if this was the correct use of an entity as I want to be sure the contact info is all stored in the Agency Contact table to limit where updates need to happen. Any help is appreciated as I have spent a significant amount of time trying to figure out and not having much luck.

Agency
AgencyID	Short Text	4
AgencyName	Short Text	50
AgencyAddress	Short Text	25
AgencyCity	Short Text	25
AgencyState	Short Text	2
AgencyZip	Short Text	10	Zip Code Validation
PrimaryContactID	Number
AgencyContact
ContactID	AutoNumber
ContactFirstName	Short Text	25
ContactlastName	Short Text	25
Title	Short Text	25
ContactPhone	Short Text	15	Phone Validation
ContactEmail	Short Text	25
AgencyID	Lookup Wizard

[orange]

When you add records to tables that have a 1 to many relationship, you must have a record in the one side before you can have related records in the many side.

1 Agency has 1 or more Contact(s)
A Contact is related to 1 Agency only
Each Agency has 1 Prime Contact

tblAgency----->tblContact

I recommend you do not use the LookupWizard with table fields.
I would create a ZipCode table, and depending on how many Agencies and zips you will have, you might consider a zip table to get standard spelling for City, State and consistency in zip validation

Some zips may include more than 1 city/town.

tblZip
zipId auto PK
City
State
zip

Also, every table should have a primary key.

Good luck with your project.

[computergirl]

Sorry, I should have given more info as I do have the primary/foreign keys set up and have gone back and removed all the LookupWizards in the table fields but am still getting the same error. In a normal situation, I would separate out the address for the reason's you recommend but in this case it is not a concern since it is not actually going to be used outside of this assignment. I went ahead and added in a picture of the portion of my relationship relevant to this question. What is happening as I am not able to add a new company or contact as it does not see a related record in the other table even though I made sure these fields were not required in the table of which they are the foreign key. My thought is I need to find a different way to handle the Primary Contact on each company while ensuring there is only one primary contact per company regardless of how many contacts the company has. Hope this clarifies more of what I am trying to accomplish. Thanks again for your response.

[orange]

You might consider

tlAgency
AgencyID auto PK
AgencyName
AgencyAddr1
AgencyAddr2
AgencyCity
AgencyStateProv
AgencyZipProv

tblAgencyContact
ContactId auto PK
AgencyID long FK
ContactFirstName
ContactLastName
ContactPhone
ContactEmail
IsPrimaryYN Yes/no

[computergirl]

I had thought about setting up that way, but this would not strictly limit to just one primary contact. If the user selected on all the company contacts, it would accept it. The textbook did explain to set up the way I did (I have read the text numerous times) but it did not show an example with the relationship, so I am sure I am missing something in one of the properties or how it is connected but have played with and nothing seems to work. I have spent days trying to figure this one thing out and can't seem to find any info online that would help. I know I could just put an contact ID field on the company profile and populate, but I also want to be sure and it will only allow contacts with the correct Agency ID that are I the contact list but am sure this would not be the best way to handle. Appreciate any other suggestions as I am just not sure what to try next.

[orange]

Perhaps the textbook has some other info, or is focusing on some specific fact or concept.

I could see how you could have multiples, but then that sort of negates the word Primary.

I could see making a unique composite index of
ContactId auto PK
AgencyID long FK
to prevent duplicates.

You could have a verification routine for the table to ensure there was only one IsPrimaryYN= True for
each combination of ContactID and AgencyID.

I could even see a AgencyContacts being broken into distinct groups such as

NonPrimeAgencyContact and PrimeAgencyContact

Can you send jpg of text book article that may shed some light on the issue?

[computergirl]

It is on pages A7-A8 of the following link but I have pasted below. This is what I was trying to duplicate:
http://www.cengagebrain.com/content/..._01.01_toc.pdf

[robbeh]

I couldn't really understand the examples above (I am relatively new as you are) but from the text page the way I see it is:

DeptNum is PK
DeptHead FK ONE-to-ONE to the EmployeeName
DeptName

EmployeName PK ONE-to-ONE to the DeptHead
Etc...

So wouldn't your AgencyID in the first table be your Primary Key and then your AgencyId in the second table be your Foreign Key to connect the two?

Now I am confusing myself but I think it's being over complicated.

[computergirl]

That would be the case for the one-to-many relationship not the one-to-one. I do understand the article as I have read enough times but I can't get to work in practice and due to the circular reference it won't let me add a new contact or agency. In theory I understand, but know I am just missing something.

Thanks for trying!!!

[orange]

Ok I think I see the point.

There is a 1:M between Agency and Contacts
An Agency has 1 or Many Contacts

and the 1:1 between Agency and PrimaryContact

One Agency has 1 and only 1 PrimaryContact

I think the attached jpg helps. I think that's what your original jpg was trying to show- but it seemed to have an extra relationship.

In the jpg, tblAgencyContact_1 is just a second copy of the table acting as a Lookup or Reference table

Anyway, good luck with your project.