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

 

[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

 

[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

 

[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.

 

[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

 

[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