Many to Many Relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
Do you have primary keys set up on all of these tables? If not, set them up
and then try again.
-John
 
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
 
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?
Click to expand...

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]
 
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?
Click to expand...

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