(Msg. 1) Posted: Tue Jul 28, 2009 3:36 pm
Post subject: How to set up a many to many relationship Archived from groups: microsoft>public>access (more info?)
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.
(Msg. 2) Posted: Tue Jul 28, 2009 3:49 pm
Post subject: Re: How to set up a many to many relationship [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Jul 28, 4:36 pm, Jennifer <Jenni....TakeThisOut@discussions.microsoft.com>
wrote:
> 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
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.
(Msg. 3) Posted: Tue Jul 28, 2009 6:52 pm
Post subject: Re: How to set up a many to many relationship [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
"Jennifer" <Jennifer.RemoveThis@discussions.microsoft.com> wrote in message
news:A1793B1A-1FEA-4813-A1D2-8F3C40310E1F@microsoft.com...
> 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
(Msg. 4) Posted: Tue Jul 28, 2009 7:05 pm
Post subject: Re: How to set up a many to many relationship [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
"Jennifer" <Jennifer.TakeThisOut@discussions.microsoft.com> wrote in message
news:A1793B1A-1FEA-4813-A1D2-8F3C40310E1F@microsoft.com...
> 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
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office Other