Two Relationships / Same Table

[selzlerb]

Hello Team,

I'm having what I hope is a simple problem. But I can't see the solution, as the behavior is not making sense to me.

I have a database with the following fields

DB = Students

Field A = Primary Language

Field B = Home Language

The idea is that I can identify the primary language a student speaks, as well as the language that is spoken in the students home. While each field could have a different response, I want to use just one table to pull the data from.

I use a table called "Language Codes". Each record has the language "code" (00 = english, etc) as well as the code "value" in plain english.

The idea is that the code is entered in the Student database and you display the code value from the Language Code table.

The problem comes when there is an entry for the Field B = Home Language but no entry for the Field A = Primary Language. The system only works if there is an entry for the primary language AND the home language. And they both are the same language.

I've tried creating another instance of the Language Code Table and that doesn't work. I suspect I'm missing something fundamental here, but I can't see it. I've attached a JPG of the relationship tables, so that probably explains the problem better than this.

Any help would be appreciated. Thanks.

- Sez

[John Mark Osborne]

Make another Table Occurrence for the Language Code table and connect it to the Student Records TO you already have. Use the Home Language and Language Code fields as the keys. Once you create the second relationship, you should be able to place related fields from both relationships representing primary and home laguages.

[selzlerb]

Doh! I tried that before as that seemed like the logical solution. However I'm now thinking that I forgot to change the field in the student layout to reflect the change. Must have been tired.

Thanks.

- Sez

[John Mark Osborne]

I've done that dozens of times before. It's always the easy stuff that gets you :)