How do I create the appropriate relationship in a table that uses keys from two separate tables
Consider following situation:
In order to generate an offer to a guest from a set of several homes I need to pull information from:
- Contact, the contact details
- Request, the request details
… in the appropriate language, as guests come from different countries.
Each contact is always assigned one single language. When I generate an offer to a customer I need to pull also information from:
- HomeDescription, several text building blocks that are specific to a particular home and in a particular language.
- Language, several text building blocks that are language specific.
When I generate an offer, it is done from the context of Contact. I have tried several join options in order to get the description of the homes in the appropriate language, but so far:
- either I get the appropriate language in HomeDescription, but it is not the correct home, or
- or I get the appropriate home in HomeDescription, but not the correct language.
- creating a Language_ID calculated field in Request (it doesn't work)
- creating a join from Language to HomeDescription and reference those fields (it doesn't work)
Which solutions is more appropriate and how do I make it work ?
Can you advise what do I need to do to get HomeDescription to point at the right Home in the right Language ?
I can upload a sample of the database for your review if necessary.
The last question is: how would I go about existing records when I apply a new relationship to the tables, provide the ID fields are already filled in ?