Seeking Advice on Many-to-Many Relationships

[blairsly]

Hello, I am working on a database of translators. Each of these translators can have multiple Source Languages, and multiple Target Languages. I understand that I must use a many-to-many relationship, but as the Language fields for both Source and Target Languages comes from one table (tblLanguages), I am having difficulty conceptualizing how I can get the table relationship to produce the desired effect; i.e. having a datasubsheet for each translator with fields for each their Source and Target languages.

My current table structure:

tblTrans
TransID
(name and contact data)

tblLINK_Language_Trans
TransID
LanguageID

tblLanguages
LanguageID


I thought that maybe if I create a separate LINK table for each Source and Target Language, I might get the desired effect, but this was futile, as only one datasubsheet was shown.

Any advice for a humble Access beginner would be GREATLY appreciated!!!

Thanks!

Blair

 

[quest4]

Do you always jump into the deepest end of the pool? The many-to-many relationship is probably the hardest relationship to understand. But try this it may help you better understand it:
http://www.microsoft-accesssolutions.co.uk/many_to_many_example.htm
hth.

 

[blairsly]

Gasping for Air

Someone told me Access wasn't that difficult. They said the water was shallow over here, and now I'm stuggling to stay afloat.
But the optimist would also say that's the best way to learn to swim!

I had been searching around and found the link you mentioned and downloaded the example, which was very helpful, but seems a little different from what I am trying to do, in that I am trying to have 2 many-to-many's between 2 tables.

I have fooled with these structures:

1. 3 Table Option
This generated the form of the datasubset that I was looking for in the tblTrans, but when I entered multiple fields, an error occured as this would "create duplicate entries"

tblTrans
TransID

tblLINK_Trans_Language
TransID
TargetLanguage
SourceLanguage

tblLanguages
Language (One-to-Many with both Target and Source Lang. in above Table)



2. 4 Table Option
With this, I was unable to link both LINK tables to the tblTrans simultaneously (in the table, that is). They were separate datasheets....

tblTrans
TransID

tblLINK_Trans_SourceLanguage
TransID
SourceLanguage

tblLINK_Trans_TargetLanguage
TransID
TargetLanguage

tblLanguages
Language (One-to-Many with both Target and Source Lang. in each respective LINK Table)


Thank you for any advice!!

-Blair

 

[quest4]

I have been luck, I was forced to work with a many-to-many only once, with poor results, then someone pointed out to me that I had a bad design and showed me a better way of doing it and no many-to-many relationships. With that said I looked at the table aboveand maybe you might think about this:
tblTrans
TransID (autonumber) PKey

tblLINK_Trans_Language
LinkID (autonumber) PKey
TransID (long integer)
TargetLanguage (Combo) Source: Language
SourceLanguage (Combo) Source: Language

tblLanguages
LangID (autonumber) PKey
Language (txtbox)
Set like this I really can't viualize a Many-to-many relationship. Just a few thoughts from the edge. hth.

 

[Pat Hartman]

Technically you don't need an autonumber pk for the relation table. You can use the three fields as the pk. Alternatively, if you use an autonumber as the pk for tblLink_Trans_Language, then make a unique index for the other three fields.

tblTrans
TransID

tblLINK_Trans_Language
TransID (pk fld1, foreign key to tblTrans)
TargetLanguageID (pk fld2, foreign key to tblLanguages)
SourceLanguageID (pk fld3, foreign key to tblLanguages)

tblLanguages
LanguageID (One-to-Many with both Target and Source Lang. in above Table)

To make a multi-field primary key, select the first field and while holding the cntl key, select each additional column. When all are highlighted, click the key icon.

 

[blairsly]

Thank you for your help!!! Your suggestions were just what I was looking for!

With much appreciation ,

Blair