Many-to-Many relationships

[harringh]

Hi,

I have a database which contains a many-to-many realtionship and I understand that I have to create an intersection table between the two tables so that I have two one-to-many relationships instead. This intersection table only contains the two primary keys of the tables that they are linking.

e.g.
Sites table - primary key: Site Number
Reports table - primary key: Report Number
A site can belong to many reports and a report can have many sites so I set up an intersection table: sites_report

However, I am unsure as to how this intersection table is populated. When a user enters a new record for the sites table for example, how is the site number (an aplhanumeric code) put into the intersection table?

I understand that autolookup will insert values from the many side of a table into the one side, but in this case I am trying to populate the many side. As well as this I have tried using subforms but when I enter a site number for the intersection table I am told that a corresponding value does not exist in the sites table, which it doesnt. I have also attempted to use queries (append and make table) to no avail.

I am sure that there is some fundamental element that I must be missing.

Can anyone help me? If you can I would be really greatful as I've been working on this for 3 days now and Im getting nowhere.

Thanks,

Helen

 

[MoGryph]

Yeah, I found this to be a really confusing topic when I first started trying to work with Many to One to Many relationships in Access.

What the weird thing is, is that once you've figured it out, it seems like you no longer understand why you couldn't before, and you don't understand what was confusing anymore. It's like a click, and all the questions that were answered, disappeared! Does that make sense? I hope so.

I'm not sure why you want to populate the Intermediate table, when "a user enters a new record for the sites table".

Are you trying to automatically create a New Report when you're creating that New Site, and populate the Intermediate table with Both, as well?

The problem is- just like Access is complaining, you must have a record in each of the primary tables, that corresponds to the record ID's that you're trying to populate into the link table. If the foreign key you're trying to put in the link table, doesn't exist as a primary key in the main table, it's just can't create that link record.

Can you explain the layout of the form, or what you're trying to accomplish, in a step-by-step order, you know, give us a linear explaination of the process you're trying to do. I'm sure if you do, I'll be able to explain the process more, and I think you'll understand after the explanation.

I think one of my big problems when I first started was that I was using the wrong fields, in the wrong places. Could it be that, on your form where you're creating the new Site, one of the fields actually has a field from the Link table as it's control source, and you're trying to populate the Sites table with that field? Just loose that field, and correctly change the control source to the field from the Sites table. (I'm not even certain that you want any references to the Link table on the New site form?)

-MoGryph
8O)

 

[harringh]

Hi,

thanks for the responses. MoGryph, I will try to explain in a linear sequence what I am trying to do:

A user calls up the site form and wants to enter some new site information. They enter details such as the Site Number which is an alphanumeric code that is unique for each site. They also enter other information. The report that this site belongs to should already exist as if it is a new report it should have already been entered into the database.
I am using a subform so that the user can enter what report the site belongs to.

How does the Site Number value and the corresponding Report Number value go into the intersection table so that the user can perform queries on these two tables?

At the moment these new values only go into the Sites table.

I hope that this helps a little to explain the problem.

I have tried using the append query with the two primary keys as a multiple primary key for the intersection table but this doesnt seem to be populating the table.

Thanks again for the help.

 

[montrose]

harringh, Make sure that your junction tbl includes both of the primary keys from you Sites tbl and your Report tbls. Your Site Number key (alphanumeric you said, now a foreign key in your junction tbl) has to be the same field type in your junction tbl as you set p in your Site tbl. I'm assuming (perhpas incorrectly?) that your Report Number primary key is Autonumber? If it is, then in your junction tbl it needs to be set to Numeric, Long Integer, removing the default value of 0 that Access automatically puts in for default value. If not, then Report Number as the foreign key in your junction tbl now must be of the same data type as in your original Report tbl. Set both of these fields as primary keys in your junction tbl.

Base your Site form on a query. In your qry, put your Site tbl and the junction tbl. Use the ID field for Site number from your junction tbl in the design grid (not from the Site tbl). Then add whatever other fields you need from your Site tbl. Base your main Site form on this query.

Do the same thing for your subform on the Reports. Make the qry using the Report tbl and the junction tbl, using the Report number ID from your junction tbl and the Site number id from the junction tbl, and whatever other fields you need from your Report field. This becomes your subfrom that you place on yor main form. You link the master field (Site number) in your main form to the Child field (Site number) in your subform. The info will "go in" to your junction tbl. To see an example of this, look at the Order and Details tables in the Northwind sample database that comes with Access. HTH, Montrose Learn what you can and share what you know.