Many to Many

[bill crumpton]

I understand the purpose of many to many relationships and in the DB I have designed I need them. However I am having difficulty understanding how to use them. I have created intersect table that hold the primary key of one "tbl A" and the primary key of "tbl B". How do I get the table to populate with both tables keys? Do I need another field in the table, if so, where do I place this intersect table to accept data? Any help is greatly appreciated.
Thanks.

BAC

 

[Pat Hartman]

Bill,
I sent you a copy of a many-to-many sample database I made.

The intersection table is not automatically built. Access would have no way to know what records you want to relate to each other. The database sample forms show the intersection table being populated from either side of the relationship.
Pat

 

[bill crumpton]

Thanks Pat,

If I already have records in my tables can I still create the many-to-many relationship without affecting the current data? And also I only have the fields (primary kary A and primary key B) in my intersect table. I notice you have a third field. Do I need a third field? Thanks for your help Pat.


BAC

 

[Larry Schunk]

Could you please send me a copy of the example database? I am interested in better understanding how to use many-to-many as well. Thanks

 

[Pat Hartman]

Bill,
Any data that already exists in tableA and tableB are not affected by the addion of the relation tableAB. You must specifically add rows to tableAB to create the many-to-many relationship. If you add no row in tableAB to relate a row in tableA or tableB to some row in the other table, the tableA (or tableB) row will simply never appear when you run a query that pulls related rows.

Larry,
your copy is on the way.

By the way, I don't mind if you all share this sample database with other people but please, only share the original unaltered copy.

 

[Jerry Dennison]

I suspect that Pat has an autonumber field in the intersect table that is used as a pseudo primary key. The real primary key is probably a compound key made up of the two foreign keys from the other tables. By creating a compound key you insure that you do not duplicate any one many-to-many relationship combination. By having an autonumber field you're able to more easily address that specific combination of foreign keys. (not having seen his examply I'm making some assumptions, but this is the "normal" way of using intersect tables with Access)

 

[TGHockett]

Hi Pat ...

I too would love to see a sample of your work.

Or, Larry -- if you see this first please forward me a copy to save Pat some extra time

TGH

 

[Pat Hartman]

I hate to be contrary Jerry but having an autonumber key in the intersection table is not normal. The two keys (or sometimes more) that link the related tables, uniquely identify the row in the intersection table. Adding an autonumber key is redundant and does not add any value since for most purposes queries will use the key of one side of the relation or the other but not both in the Where clause. Autonumber keys should only be used when you have no other way to uniquely identify a row.

The example database actually uses three fields as the primary key of the intersection table. CustomerID, VenueID, and BookingDate. The idea is that the table relates customers to the sites they have booked for a particular day. This allows Customers and Venues to be related more than once since each relation is for only a single day. A similar example would be Students and Classes. The additional fields in that case could be SchoolYear and Semester.

 

[ywcai]

Pat,
I run into the same problem to update an intersection table. May I ask for the sample if you don't mind. Thanks.

 

[Pat Hartman]

I will need your email address to send you a copy.

 

[ywcai]

opps, sorry,
My email address is ywcai@avaya.com, or ywcai1@yahoo.com. Either one will work. Or you can send to both just in case.
Thank you very much Pat.

ywcai(Wendy)

 

[TGHockett]

A belated thanks Pat for the sample db ... can you remind me/us your thoughts on the tradeoffs of having a triple-key primary index?

I have had to resort to this method a number of times, but I always feel 'guilty' thinking there should be a better way

TGH

 

[Pat Hartman]

I know it seems like you would be better off with an autonumber PK for a table like this but you need to think about how a linking table is used to implement the many-to-many relationship. You either want to find all the rows from tableB that are related to a single row in tableA or all the rows in tableA that are related to a single row in tableB. In one case you have the key to tableA and in the other you have the key to tableB. Only in an update situation would you potentially have both keys. There is never a case where you would have the arbitrary autonumber key since there is no table you could store this value in that wouldn't cripple the relationship. You can't store it in tableA because it has a one-to-many relationship with tableA and you can't store it in tableB because it has a one-to-many relationship with tableB.

 

[ywcai]

Hi, Pat..
Thank you very much for your example program. It is very helpful.
Regarding the example you sent, here is another question.
How did you make multiple fields shown in the combo box (in the subform)? The combo box is bounded to VenueID, but the fields shown is the VenueName, City and St. Why is it so and how?
ywcai

 

[ywcai]

Pat,
I found the combo information in access help. Thanks a lot.
ywcai