Access 2007: expand option of a row in a table fails

[Margie]

Working on my movie database, I've yet again encountered 2 related problems that I just can't seem to fix.

I've build a table (called Movies) around which I build the form. In the table there are two rows. One for the production company (called: Production) and one for the distribution company (called: Distribution). In the form I made there are two fields with the same names as in the table. Both fields are linked to another table (called: Companies) and draws the info from there. When I go to the table and expand a row, it only shows the movies that have the same company in both fields. If either difffer, then it won't show. Here is a picture of what I mean.

What must I do so that, in this case 'Warner Bros.', shows all the movies, whether they're the production company, distribution company or both.

The other 'similar' problem is that with 1 table (called: Score) there isn't any expand option at all. Something that I would really like to see.

I hope all is clear and that the pictures help. If I've given too little information, please mention so.

Greetings Margie

[Margie]

I found out that the second problem I was talking about had to do with subdatasheets and that it was caused because it had no one-to-one relationship. I succeeded on my own (which I always find more enjoyable) to fix that problem. That leaves only the first problem:

the subdatasheets aren't showing info if the fields have different values. Could it be that it needs a one-to-many relationship or something between the fields and the main table? Going to continue working on that.

[Margie]

Still no success but I think I now know how to explain it using proper terminology.

I have a subdatasheet in a table (called Country) that's in a one-to-many (3) relationship. The Subdatasheet Name in the Property Sheet of the table is set to Auto and it's linked to three other columns (Country 1 / Country 2 / Country 3) located in table called Movies. So far so good but the subdatasheet only show's information if all three colums have the same values in a row. Example: if all three contain U.S. I get all the info I wantfrom the table Movies. But if this isn't the case (U.S. / France / Germany) then no info at all is linked.

If I go to Insert Subdatasheet, it shows in the Link Child Fields 'Country 1;Country 2;Country 3 and in the Link Master Fields Country;Country ;Country.

Anybody?