How to set up a many to many relationship

[Jennifer]

I'm trying to setup a database with various business contacts and their
information. I have created the table of contacts with their info. The
problem is that each contact is involved in different committees. I want to
be able to input the diferent commitees that each contact is involved in and
then be able to filter out my contacts by commitee. I assume I cannot use
only one table for this, considering that you can only input one thing in
each field for each contact. So basically I want to set it up so that each
contact is connected to various committees and each committees is connected
to various contact. Going through tutorials, its seems that I have to creat
multiple tables and then create some sort of many to many relationship
between them. I don't know if I should create another table or what fields to
include where.
Basically in the end I want to be able to have a form where I can input the
contact and their information and all the committees their involved in. But I
want to do in a way that I can later on filter out my contacts by commitee.
Please help with this. Is this even possible or am I thinking about it in the
wrong way? If it is possible can please explain what I have to do and
especially HOW TO DO IT step by step. I am not a genious with computers and
its the first time I deal with access.

Thank you
Jennifer

 

[Steve Schapel]

Jennifer,

This is not a complete answer, but you may be interested in this brief
article:
http://accesstips.datamanagementsolutions.biz/many.htm

 

[Keven Denen]

I'm trying to setup a database with various business contacts and their
information. I have created the table of contacts with their info. The
problem is that each contact is involved in different committees. I want to
be able to input the diferent commitees that each contact is involved in and
then be able to filter out my contacts by commitee. I assume I cannot use
only one table for this, considering that you can only input one thing in
each field for each contact. So basically I want to set it up so that each
contact is connected to various committees and each committees is connected
to various contact. Going through tutorials, its seems that I have to creat
multiple tables and then create some sort of many to many relationship
between them. I don't know if I should create another table or what fields to
include where.
Basically in the end I want to be able to have a form where I can input the
contact and their information and all the committees their involved in. But I
want to do in a way that I can later on filter out my contacts by commitee.
Please help with this. Is this even possible or am I thinking about it inthe
wrong way? If it is possible can please explain what I have to do and
especially HOW TO DO IT step by step. I am not a genious with computers and
its the first time I deal with access.

Thank you
Jennifer

Jennifer,

You will have three tables to express that relationship.

ContactTable (key ContactID)
CommitteeTable (key CommitteeID)
MemberTable (name not important, just used member since contacts are
members of committees) (Keys ContactID and CommitteeID)

You can then use the relationship tool in Access to connect Contact to
Member by ContactID (1 to Many) and connect Member to Committee by
CommitteeID (1 to Many).

Subforms are good ways to express any 1 to Many relationship.

Keven Denen

 

[Gina Whipp]

Jennifer,

Create two more tables, since you created one table you should be able to do
this...

tblContactCommittees
ccContactID (FK - Join to your tblContacts)
ccCommitteeID (FK - Join to tblCommittees)

tblCommittees
cCommitteeID (PK - Join to tblContactCommittees)
cCommitteeName

Then create a subform and use tblContactCommittees and use
tblContactCommittees as the RecordSource. Make a combo box for
ccCommitteeID, use the wizard and make ccContactID invisible. Attach
subform to main form using ContactID and the links. You should then be able
to enter many committees per contact.

Since you are beginning these links might help...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm