I am using Access 2007.

I have been doing some testing with regard to Many to many relationships. I have created three simple tables with only a few records in them. Please see .pdf attachment. The three tables are Artists, Recordings, and Artists_Recordings. This last table is the link table for the Artists and Recordings tables.

The example is for a ficticous publishing company who wants to keep track of their Artists and what recordings they have made. It is a typical many to many scenario. I entered the data into these table manually.

I have the three tables, I am able to do queries on the tables and get back just the records I am intersted in, just as is explained in most SQL books. However, what is not clear to me is how to make use of this, practically, in an Access application. That is, I have an Artists table. I have a recordings table. I suppose if this were a real app I would have an Artists form and a Recordings form where I would put in names of artists and names of recordings.

But what is not clear to me is the idea of how this information makes it into the link table. How do you keep the Artist table, the Recordings table and the Link tables in sync? The link table has a compound index on Artists and Records. I did this because according to other threads in Utter, this is what I am supposed to do. But, if you enter an Artist, for example, in an Artist form, and behind the sceens, it inserts a row in the link table referring to that artist, what then do I do when I enter a recording that was made by that artist?

I am obviously missing some pieces in this puzzle. Was wondering if someone could point me in the right direction?

Thank you

Normally, with a many-many relationship, I tend to do something like this:

The parent form is bound to one-side table (e.g Artists for example)
The subform is bound to the junction table (Artist_Recording). I hide the Artist ID because it's linked)
On the subform, I then place a combobox (could be listbox) that has the rowsource bound to other one-side table (e.g. Recordings)

So the entry process becomes this:

A user select a artist then enter the subform and selects recordings from the list.

Of course, you need to handle the cases where you may want to add new recordings, and that could be either done by a separate form for Recordings, or use combobox's NotInList event.

Did it help?

I need to digest what you've said and will respond with more understanding tomorrow. But one thing that comes to mind now is, what about the situation where you want to enter recordings seperately. That is, a recording (like I walk the line) may have been recorded by several different artists. I suppose you do the same thing that you suggested in your response, except that the form we're talking about would be a recordings form and the sub form would contain artists that have recorded that particular song.

Now that I think about it, I may have used the wrong verbiage. Perhaps the tables should be Artists & Songs instead of Artists and recordings, but you get the gist.

Thank you for responding.

Well, the approach could be changed so that you have a single form with two comboboxes. The form would be then bound to the junction table, while each combobox's rowsource is bound to each one-side table and you can select from the combobox.

You would add new artists and recordings (or songs) in a separate forms and "associate" them with the form I just described in previous paragraph. You can also use NotInList as well.

Willie Nelson and Johny Cash in the same database. How could I possibly resist?

This one uses a techniques borrowed from MS templates, including embedded macros.




Edited by: GroverParkGeorge on Tue Sep 8 22:43:07 EDT 2009.

I changed the demo a bit, I think it might be helpful as a starting point.

WoodyAh, that makes all the difference!

Well, in that case, the YearRecorded field is in the wrong table.

So, for clarity, if you rename the Recordings table to Songs, and the rename the Artist_Recordings table to Recordings ('cos that's actually what you are talking about), then YearRecorded will be in the Recordings (was Artist_Recordings) table.

You may be interested in this article:
http://accesstips.datamanagementsolutions.biz/many.htm

On my website ( www.rogersaccesslibrary.com) there is a small sample database called: ImplementingM2MRelationship.mdb, which illustrates a couple of ways to implement a many to many relationship in an application. You can download it for free here: http://www.rogersaccesslibrary.com/forum/f...sts.asp?TID=342

Well, thank you all for your input. It is going to make the difference. I have downloaded your files and been to your sites and it is very helpful. I might mention though, Steve, I get an Application Firewall Alert when I try to access your site.

Thank you in particular George for taking the time to show me a solution using my own logic. I have examined your application and think I understand what's happening. I think mentioning some of the good ole boys brought out the sympathy in you. Thank you again.

I will report back when I've successfully impletmented your suggestions.

Thanks for letting me know about the problem with accessing my website, Woody. I will see if anyone else experiences the same.

It works fine for me.

George,

I had a question with regard to the demo you sent me. I noticed that YearRecorded field was placed in the Link Table. I was wondering what the reason for that was. I suppose you may have a reason that I am unaware of.

At first glance it seems more logical to have it in the recordings table. With it in the Link table it requires you to put the date in with each newly added row to the detail table of either form; which, you may not be able to remember, or you may enter incorrectly. Was this intended or is it a typo?

Thank you again for your help.

Basically for the same reason as Steve gave.

The "recording" of a song is an event. That event is tracked in the ArtistSong table, which might actually be better named "recording", as was also noted.

The song itself exists independently of whether it is EVER recorded by an artist.

The artist exists independently of whether he or she ever actually gets to record a song.

"YearRecorded", in other words, is an attribute of the event in which a song and an artist come together in a "recording".

Woody,

George has explained it very masterfully.

In other words, this is not different from the design of all tables in a database. A field goes into the table which describes the entity that the field's data is an attribute of. And the recording date is an attribute of the recording.

That was the exact reason that I referred you to my article, so it's a pity you can't see it. There is a tendency to be dismissive of "the Link table", and I think I did detect this in your post. But the so-called link table describes an entity in its own right, and thus stands tall and proud, which is what my article is about.

>"YearRecorded", in other words, is an attribute of the event in which a song and an artist come together in >a "recording".

Understood. When the information you're trying to track occurs at the time the two entities come together, the best place for it is in the link table. Thank you both.

Steve, I would like to read your article. I was in a bit of a hurry yesterday and did not take the time to understand why I was not getting to your web page. At first I thought it might just be a setting on my machine that needed to be changed with my security settings. However, afte rlowering my settings and trying again, I was still unable to get in. Any suggestions?

Hi

The following is the link to the article

Hope this helps?

Mike

Thank you Mike

When I click the link I get the same message I spoke of earlier. I am not clear as to what is happening, but the message is as follows:

Application Firewall Alert

Your request triggered an alert! If you feel that you have received this page in error, please contact the administrator of this web site.

To immediately resolve this problem. You can login to your hosting control panel->security->Security guard and set the security guard to Medium Level for your domain name.

Hi

THis just means that you have your Internet Explorer Security Settings set at High

If you change the setting to Medium/High then try the link it should be fine

Regards

Mike

Hi Mike

This is part of what I was talking about earlier. I already have my Internet Security Settings set to Medium/High. Upon receiving the message I then set them to Medium, which is a low as my Internet 8 Explorer settings will allow me to go. Even after setting them to Medium, I still get the same message.

If it is of interest, I seem to be re-directed when I click on your link. That is, instead of going to

http://accesstips.datamanagementsolutions.biz/many.htm

I am taken to http://www.webhost4life.com/firewall.htm, where the message I mentioned above appears. If I try to add http://accesstips.datamanagementsolutions.biz/many.htm to my trusted sites list I am told the address must start with https. When I pur in https and try to add I get the following message.

There is a problem with this website's security certificate.

The security certificate presented by this website was issued for a different website's address.

Security certificate problems may indicate an attempt to fool you or intercept any data you send to the server.
We recommend that you close this webpage and do not continue to this website.
Click here to close this webpage.
Continue to this website (not recommended).

Woody -
Sounds like you may have issues at your workplace (if that is where you are). You may have to look when at home. Or, get your Network Admin involved.