Many to Many Relationships

[Guest]

Hi,
I am trying to create a many to many relationship between two tables, and am
currently having no luck whatsoever, Is there another way? I have completed
the steps shown in Help until:
"7. Define a one-to-many relationship between each of the two primary tables
and the junction table. The junction table should be at the “many†side of
both of the relationships you create. "
when I try to create a m2m relationship by dragging into junction table, the
relationship is one2one rather than one2m. COuld you please help?

 

[John Welch]

Do you have primary keys set up on all of these tables? If not, set them up
and then try again.
-John

 

[Guest]

Ok, stupid me just worked out what I was doing wrong, but now, after
completing a form and subform, the entire contents of the table appears on
the subform. The story is: I have a database of members of my unit, but
members within my unit may have several ranks/positions eg secretary,
treasurer, permit officer etc. How do I set the db up so I can select the
positions (and only those Positions) that relate to the member in question?

Jarrod

 

[John Vinson]

Ok, stupid me just worked out what I was doing wrong, but now, after
completing a form and subform, the entire contents of the table appears on
the subform. The story is: I have a database of members of my unit, but
members within my unit may have several ranks/positions eg secretary,
treasurer, permit officer etc. How do I set the db up so I can select the
positions (and only those Positions) that relate to the member in question?

A table of Members, primary key MemberID (or whatever...);
a table of Ranks, primary key RankID;
and an (initially empty) table of MemberRanks. with MemberID and
RankID as the only fields.

Base a Form on Members, with a subform based on MemberRanks. On the
subform have the Master/Child Link Field by the MemberID and include a
Combo Box based on Ranks, storing the RankID.

See the Orders form in Northwind sample database for an example:
Members = Orders; Ranks = Products; MemberRanks = OrderDetails.

John W. Vinson[MVP]

 

[jacksonmacd]

Sounds like you inadvertently indexed the junction table field with a
Unique index on the joining field. A one-to-one relationship would be
created if the fields in both tables in the relationship have Unique
indexes. Without the Unique index, a one-to-many relationship would be
created.

In the junction table, change the "No duplicates" to "Duplicates OK"
in the index definition of the joining field.

Hi,
I am trying to create a many to many relationship between two tables, and am
currently having no luck whatsoever, Is there another way? I have completed
the steps shown in Help until:
"7. Define a one-to-many relationship between each of the two primary tables
and the junction table. The junction table should be at the “many” side of
both of the relationships you create. "
when I try to create a m2m relationship by dragging into junction table, the
relationship is one2one rather than one2m. COuld you please help?

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security