Link Master and Child Fields - Many to Many

[Delid4ve]

Ive done alot of reading and am starting to understand to record source, control source and row source values. However im struggling with the master/child relationship.

If i have 3 tables to which my form is produced from:

Documents
DocumentID(PK)
....

DocumentDetails
DocumentID(PK)
CylinderSerialNo(PK)
....

Cylinders
CylinderSerialNumber(PK)
.....

and the cylinder form is a sub form within the document main form, what should be the link fields and why?

Im struggling to find a clear 'google' explanation for a many to many relationship

 

[gemma-the-husky]

it is not possible to model a many-to-many relationship in a database. It has to be decomposed into 2 x 1-to-many relationships

you have documents and cylinders. A many-many relationship implies that each document (can) includes many cylinders, and each cylinder (can) be on many documents.

so you need a joining table - the document details table - to link the 2.

you may have

document 1, cylinder A
document 1, cylinder B
document 1, cylinder C
document 1, cylinder D

document 2, cylinder B
document 2, cylinder D

document 3, cylinder B

document 4, cylinder D

----
this data can be equalled displayed/sorted in terms of the cylinders

document 1, cylinder A

document 1, cylinder B
document 2, cylinder B
document 3, cylinder B

document 1, cylinder C

document 1, cylinder D
document 2, cylinder D
document 4, cylinder D

the way you choose to design your forms is a matter of taste. you may want to see the documents as a main form, and show the cylinders related to each in a subform. As you move from document to document, the related cylinders change automatically to reflect the underlying data. Alternatively you may choose to display the cylinders as the master, and documents as a sub form. You will probably have both.


-------------
You design queries based on your tables to extract the data you want to see.

so if you want to display documents, with cylinders as a subform

Main Form - based on Documents table

SubForm - based on a query joining the Junction Table with the Cylinders Table (to obtain details about cylinders)

The link is the documentID from the main form, and the DocumentID in the subform.

So that your system shows the cylinders related to the selected/active document.

I hope that makes sense.

----------------------

I would not class your structure as a master-child relationship. A master child relationship is (I would suggest) a 1 to many relationship. In your case each entity could be a master of the other, which is why it is many-to-many

A customer has many orders, and is therefore the master of the orders. An order can only have one customer. You should not be allowed to create an order without first having a customer.

 

[jdraw]

Further to Dave's advice, I'd recommend you watch a few youtube videos on
* 1 to Many relationship which is your Master/Child or Parent/Child, and

* Many to Many relationship which is resolved to two 1 to Many relationships by means of a Junction/Linking/Join/Associative table for processing with relational database.

Steve Bishop has a video on Many to Many that may be helpful.

I recommend you watch a few videos to get some different perspectives on the concept.

 

[Delid4ve]

Hi Guys

As i showed in my original post, i have a join table already, i understand this.

It was the master child fields i dint understand and also because im using two combos in the cylinder sub form from two seperate tables i was struggling to get this right.

However, i've now solved this myself. Previously i couldnt get the form, either by manual design or by wizard to work correctly. By trial and error ive found why:

A: the join table needs to be in the record sources for both tables
B: the subform cylinderID needed to save to the join table - wizard wasnt doing this
C: the child/master link was across documentID to the join table

Everything now works as required.

 

[jdraw]

You may find this useful. The site has many Access related tutorials.

Glad you have the issue resolved.