I'm very interested in learning to use many to many relationships. I have already learned how to create them between tables, but I'm having trouble creating forms to input records into these tables. I prefer not to use queries at this point, if that's at all possible. I am an extreme novice with access, so I need a fairly simple solution that does not involve any programming or anything else that advanced. Can anyone suggest a good source of info on how to create forms for Many to Many relationships? Videos would be best, but I'm having trouble finding even a website that explains a detailed solution to my problem, so I'll take anything I can get.

Thanks,
Justin

The very short answer to your question is that you don't use many to many relationships in ANY relational database. EVER. You break them down into two one-to-many relationships and use those. The reason you probably can't find anything is that you're trying to do something that just won't work. Think about it for a minute. If you have a real M-M relationship... how do you know how to get from from the "one" side of your relationship to the "many" side? You don't.

What business problem are you trying to solve? Maybe starting there will help...

Well it's not a business problem, and it's likely to be controversial to mention the actual subject matter of my DB. So let's use one of the examples I have come across many times tonight. Let's say we have one table called, "Albums" which lists all of the albums I own. We have another table called, "Songs" which lists all of the song recordings I have in those albums. One album will have many songs, and the same recording of the same song can be on more than one album. I already know that to get the "many to many" relationship I have to create a table "in between" the two tables. So if "Albums" is A, and "Songs" is C, I have to have a table B containing foreign keys to each of the master keys of A and B. So it's really just two "one to many" relationships intersecting at one table. I'm just trying to figure out what my options are for inputting data into these tables using a form.

I read another forum’s thread (http://forums.techguy.org/business-applications/691746-solved-many-many-relationships-test.html) in which the user cristobal03 suggests that a Relationship is only a “constraint." It is not “proactive. So the child field is not automatically given the value of the master field.

Before reading the thread, I did assume that the Referential Integrity forced the value of the master field to the child field. Now it seems like the Link Master Fields and Link Child Fields in the properties must make that happen, but I have not figured out how to produce that result outside of "one to many" relationships.

In the above mentioned thread, cristobal03 also suggests cascading rules (updates and deletes) are the means to create a proactive link between fields.

Are cristobal03's claims valid?

Let me know if I'm being unclear about what I'm trying to accomplish. There is enough info out there about creating "many to many" relationships in access that there must be some about actually using those relationships, and I'm not finding it because I'm just wording my Google searches incorrectly. So if you can point me in the direction of some of that info, I can at least gain a little understanding. The more I learn about this subject, the more clearly and concisely I will be able to ask my question.

In the mean time, I will research how to use cascading updates and cascading deletes. Maybe my answer is there.

Thanks for the reply,
Justin

Justin,

Using your example, if it was me, I would remove all duplicate songs with the same artist from the song table. This would give you the one-to-many relationship. If you then ran a query for that song it would return all the albums it was on.


Just my opinion


bob

Hi Bob,

Wouldn't the song - artist relation be a many-to-many relation?

Since the album situation is just an example, I will substitute another example. In an educational setting there are courses and students. One student may take many courses, and one course may be attended by many students, so a junction table is set up with foreign key fields that link to the primary key fields in the students table and the courses table.

Typically in a many-to-many setup one table tends to be active and the other static. In context, you start with a list of students, then assign them to classes. You do not create a student record at the time they enroll for a class. In terms of the interface, you would have a main form bound to the Courses table, with a subform bound to the junction table. A query based on the table is the same as using the table itself, so no need to worry about that. It can be more complex than that when queries are based on more than one table, so for now I will just assume the main form is based on a query that is based on the Courses table. However, in a query you can sort the data as you choose.

The subform would have a combo box, bound to the StudentID field in the junction table, that gets its Row Source from a query based on the Students table.

I suppose it is accurate enough to say "a Relationship is only a constraint. It is not proactive. So the child field is not automatically given the value of the master field." The relationship does not automate that process. However, the subform control (the "box" on the main form that contains the subform) has Link Child and Link Master properties. These are set to the names of the linking fields. In this way the child record (based on the junction table) inherits the primary key value from the parent record.

Cascading deletes can be useful so that you can delete a parent record and have the associated child records be deleted also. Without that you would have orphan child records, which Access tries not to allow. I have had little use for cascading updates. I think they would be most useful if the relationship is on a "natural" key rather than autonumber or some other "surrogate" key. However, I don't think your answer lies there, but rather in using the available interface tools to present the related data in a way that is accessible to the user.

I will just say that the situation I have described is not necessarily as simple as I have presented. A course has sections, and each section has an instructor, who are in their own table. The many-to-many may be between sections and not courses, but the principle still applies. I have assumed a simplified version of what would typically be found in the real world since it is just an example.

yves,

Depends on how you want to look at the data, on the face, you are correct. However, in this specific case I would consider song and artist one field. Think of the song "The Impossible Dream" from the broadway show Man Of Lamancha. It has been done by at least 20 people if not 10 times that amount. Wouldn't it be better to have that stored the 20 times ? Guess it falls into that 9 different ways of doing it realm. It may not be fully normalized but in this case, I think it would work better. Or at least now worse.


Agree to disagree ?



Bob

You might get some ideas from Singing Cowboys Demoon my website. It's based on artists and recordings and demonstrates how to set up tables and create forms to work with them.

HTH

George

As been pointed out, in a classic in traditional sense, there’s really not such a thing as a many to many relationship between two tables, however by cobbling together related tables one after another, you do at the end of the day due in effect get the same result of a many to many relationship, but technically that’s not what occurring between two tables.

Let’s assume that each weekend we have to take in a bunch of donations from people. This means we have one main table with information about the date and time etc of this event. Our next table would have people and their donation amount. The next table after that would be to take a donation amount, and split up their funds into different accounts . This is a classic accounting distribution problem that just about every accounting package from Quickbooks to whenever has to implement. As it turns out, this classic distribution problem can be solved with very little code when using access.

The trick to modeling these types of forms is to use several sub forms placed into one main form.

The following form shows this:

(IMG:http://www.members.shaw.ca/AlbertKallal/Articles/grid2.gif)

If you look at the top there’s a main record with information about this batch run and date and time. Now, on the left side is many people and a donation amount. And on the right side is that donation for the one person split out to many different accounts. So I’m displaying many people, and I’m also to display the many accounts that a donation may be split out to .

Keep in mind that access does not let you place a continuous subform inside of a continuous subform. However, you can certainly place two continuous of forms beside each other as the above screen shot shows to model the same affect that you desire.

There’s also surprisingly as mentioned very little code to manage this whole thing.

For the left side continuous form, because the link master and child settings are set to the main form record, then NO code is required for this form to be populated with data. However, for the right side continues form to follow and display the correct Accounting Data for each person in the leftr side continuous form, access will not automatically do this for you . However, a simple bit of code in the on-current event of the left side form will forced access to wake up and note the right side continues form has to be RE populated to display the donation accounts for that one particular person .

The one line of code placed in the left side on-current event will fix this:


The link master/child settings on the right side continuous form I use:

linkChildFields main_id (whatever is the name of the field in
this sub-form that is used to relate back to the parent table)
LinkMasterFields [MasterFormLeftSide].[form].[ID] ("masterFormleft" is the name of
the subform contorl you used to hold the continuous form on the left side ).

So, you’ll need a tiny little bit of code, but not a whole heck of a lot, and you now have a screen that displays many information related to many information.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

Albert,
[quote] me.Parent.Child2.Requery. [quote]
I assume I am not supposed to type this in verbatim. So what info needs to be substituted for each part?
[quote] The one line of code placed in the left side on-current event will fix this: [quote]
Does this mean you would put this code in the on-current event in the subform to the left?
Thank you for this info. I just started learning access last week and liking subforms together was actually my first problem (Sub Form within Sub Form; LinkMasterFields/LinkChildFields between Subforms). I ended up using text boxes in the main form to pull values from subforms, then using those values as masters to other subforms. So if what you’re suggesting would accomplish the same outcome, it is a much more elegant solution.

Bob,
Thanks for the suggestion. However I used Albums and Song Recordings for very specific reasons. The same song by a different artist would be a different recording, as would the a different version of the same song by the same artist. The same recording of “Outshined” by Soundgarden is on both the album, “Badmotorfinger” and the album “A-Sides.” I would consider that song recording to only need one entry.

George,
Thank you for the file. It looks good. I can probably figure out how it works if I explore it long enough, but is there any info on your website that details exactly how this was created?

Finally,
I thought of a real (and uncontroversial) example of how I will use many-to-many relationships. I have two spreadsheets that could be joined together into one database. One is just my list of contacts (name, phone, address, etc) the other is a list of communications (if I think I will be able to learn from it in the future, I will take notes on any communication I have with another person or group). One person may be involved in many communications with me, and one communication might involve more than one other person. I attached a DB with fake info to illustrate what I’m describing.
- In the table “People” I have the master key “PersonID” and the fields “PersonName” and “Address.”
- In the table “Communications” I have the master key “CommunicationID” and the fields “CommunicationSubject” and “CommunicationNotes”
- I also created a junction table and the appropriate relationships, incase that is necessary.

I have already created a form into which I can input people. What I need is a form that will allow me to create a communication entry and add to it more than one person from people from my People table. I would then like to be able to select any person involved in that communication and see their address. I would also like to be able to pull up all of the communications in which a single person was involved.

Does anyone know how to accomplish this?

Thanks to everyone for all the help so far,
Justin

Yes you have this correct. Keep in mind that when you drop a sub form on a form, you’re not actually placing the real form on the form, but are in fact placing a control on the form. This is a significantly important concept to grasp when using sub forms.

What this means is that the name of the control you drop on the form, does not have to have the same name as a sub form you are going to be viewing. In a nutshell, this is the same idea as to when you drop a text box on a form. You THEN choose what data the text box will show from an underlying column in the table. While the wizards most of the time give that text box the same name as the underlying column in the table, it’s not a requirement. In fact some developers by habit always use different names for their text boxes so the underlying column, and referencing a text box on the form are two different objects .

I’m just pointing out the above, because the text box you drop on a form, or in this case the sub form control that you drop on the form (which as I stress is a control and NOT a form) could have any name you want. It is the setting of the text box or the sub-form control that you set what it will display.

Having clarified the above, then the syntax as you correctly note goes in the on current event on the left side form. I will be as :

Me.Parent.NameOfYourSubFormContorlGoesHere.Requery




Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

I just noticed that my file did not upload last time. So here it is.

I will address part of your question. In the setup I suggested for courses and students, substitute the Communications table for the Courses table, and People for Students. You already have a form for entering people, which is good. Create a form based on the Communications table (frmCom), with a subform (frmComPeople) based on the junction table. The name of the subform control (the "box" containing the subform) is fsubComPeople. Set the Link Child and Link Master fields of fsubComPeople to the name of the linking field between Communications and the junction table (PeopleID?). On the subform, create a combo box (cboPeople) with the Row Source based on the People table. Typically it would be something lke:

SELECT [PeopleID], [LastName] & ", " & [FirstName] AS FullName FROM tblPeople ORDER BY [LastName], [FirstName]

Make sure the Row Source column with PeopleID (the first column in the above) is the Bound Column. The column Count is 2, and the Column Widths something like 0";1.5"

Now create another form (frmInfo) based on the People table. Add address, etc. fields.

Add a subform control (fsubInfo) on the main form. Use frmInfo as its Source Object. Set the Link Child property to PeopleID, and the Link Master property to:
Forms!frmCom!fsubComPeople.Form!cboPeople

Finally, in the Current event of frmComPeople:
Me.Parent.fsubInfo.Requery

This is essentially what Albert described, but with a few more details, including how to see the address info for each person in the subform.

I have named everything to make it as clear as possible here. Forms have the prefix frm, and subform controls have the prefix fsub. Tables have the prefix tbl. Use whatever names you like. The idea with the last part I described is that you are linking PeopleID on the frmComPeople subform (based on the junction table) to PeopleID in frmInfo. The Requery code tells Access to set the linking field to the name of the selected record on fsubComPeople.

As for seeing only the records with a single person, I can think of a possible way using domain functions, but I'm not at all sure it's a good way, so I will leave it to others to weigh in.