Many to Many Relationships

[ericwalton]

Hello - I'm working on a database that has some many-to-many relationships.
I've used Access several times however I am fairly new to creating databases.


I understand that the tables which have the many-to-many relationships need
to be joined through a 3rd (juntion) table so I have set that up. I used the
key fields from both tables and set them both as key in the juntion table. I
made one join from each table to the foriegn key in the junction table.

I am getting confused though when it comes to a few things.

I would like to show the multiple records as a subform, only showing records
applicable to the open record in the main form. Would the information from
the "many" side in the subform come from the junction table or from the other
main table attached to the junction table?

I can't find any reference material to explain this any further. If anybody
knows of a good source, let me know.

Thank-you,
Eric

 

[John W. Vinson]

Hello - I'm working on a database that has some many-to-many relationships.
I've used Access several times however I am fairly new to creating databases.


I understand that the tables which have the many-to-many relationships need
to be joined through a 3rd (juntion) table so I have set that up. I used the
key fields from both tables and set them both as key in the juntion table. I
made one join from each table to the foriegn key in the junction table.

That's correct.

I would like to show the multiple records as a subform, only showing records
applicable to the open record in the main form. Would the information from
the "many" side in the subform come from the junction table or from the other
main table attached to the junction table?

From the Junction table - or, sometimes, from a query joining the junction
table to the other "one" side table.

It's very common to use a continuous Subform with a Combo Box control. This
control would be bound to the foreign key to the "other" one side table, but
would display the human-meaningful text value.

I can't find any reference material to explain this any further. If anybody
knows of a good source, let me know.

The Orders form from the Northwind sample database which came with Access is
one good example. The mainform is based on Orders; the subform is based on the
many to many resolver table named OrderDetails; it stores the ProductID in
OrderDetails while displaying the product name.

See also these tutorials and resources:

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

John W. Vinson [MVP]

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImplementingM2MRelationship.mdb" which illustrates how to do
this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L