How do I create a many-to-many relationship

[TresVinos]

Hello,

I'm a bit rusty. Could someone please refresh my memory as to the proper setup of tables for a many-to-many relationship?

I have a list of members and a list of committees. Members can be on more than one committee, and committees can have more than one member.

Fields in tblMembers are: MemberID, Name, Address, JoinDate
Fields in tblCommittees are: CommitteeID, LongName, ShortName, CreatedOn, Type

Do I create a another table that sets up the relationship (i.e. just two fields: MemberID and CommitteeID)?

Thanks!

Rafael

 

[TresVinos]

P.S. - What I want to end up doing is seeing all the members under a certain committee, and all the committees under a certain member.

 

[PHV]

Create a junction table, say tblMembersCommittees, with at least MemberID (FK referencincing tblMembers) and CommitteeID (FK referencincing tblCommittees)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TresVinos]

Thank you! I'll try it =)

 

[wvmbark]

PHV,

What establishes the FK? Is only the fact that data from the PK field will be in there? Or, is it established by joins in the relationship window? Or, is it only really FK when joined in a query (I.e. for recordsource in the data entry form, etc.)?

Thanks!

Mike

 

[PHV]

I'd play with the relationship window.
Have you created the indexes allowing duplicates ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886