Multiple identical relationships in one Access 2007 table

10 pts.
Tags:
Access 2007
Access 2007 form creation
Access 2007 forms
Access 2007 functions
I am building a volunteer contact management database. I have a table titled “Volunteers” that contains contact information fields and links to other databases with information about hours accrued. The Primary Key in this table is “VolunteerID”. I want to somehow be able to associate different volunteers with each other in “Households”, another table. My problem is that I cannot create a table that contains this information: HouseholdID (Primary Key) VolunteerID1 (linked as a foreign key from the primary key in “Volunteers”) VolunteerID2 (also linked as a foreign key from the primary key in “Volunteers”) Is there any way to create a relationship here between VolunteerID1 and the VolunteerID field in the “Volunteers” table, and between VolunteerID2 and the VolunteerID field in the “Volunteers” table? Or is there a simple solution to this contact management problem that I am missing? Thank you!

Software/Hardware used:
Microsoft Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

You want to create a table with a two column primary key.

<pre>CREATE TABLE HouseHoldVolunteer
(HouseHoldId INT,
VolunteerId INT
CONSTRAINT PK_HouseHoldVolunteer (HouseHoldId, VolunteerId))
</pre>

This way you can have multiple volunteers for each household. You then need a HouseHold table which contains the information about each household.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following