Different fields related to the same field

[setis]

Hi all,

How can I make the attached query to work?

As you can see I have 3 country fields related to the country table.

How can I get the country name for every field on the results?

 

[isladogs]

Your post is a masterclass in not giving a clear request!

1. Country table? Do you mean tblCountries?

2. Which 3 fields are you trying to extract & from which tables?

3. Your screenshot doesn't show the name of the table on the left.
Please repost showing all the table or better still post your db

 

[setis]

Your post is a masterclass in not giving a clear request!

1. Country table? Do you mean tblCountries?

2. Which 3 fields are you trying to extract & from which tables?

3. Your screenshot doesn't show the name of the table on the left.
Please repost showing all the table or better still post your db

Sorry about that.

* My main table has 2 country fields related to tblCountries
* The ProviderDatabase tbl has 1.

If I run the query without the tblCountries I get the PKs in the results.

I am having issues when more than 1 field is related to the same table like in this case and I would like to be able to get the results on the query for "CountryTo", "CountryFrom" and from the ProviderDatabase, "Country"

Thanks in advance

 

[pbaldy]

You add the country table twice, and relate each field to a different instance of it.

 

[Pat Hartman]

To expand on pbaldy's suggestion.
Each time you add the country table to the query window, Access will suffix the name to make it unique so you'll end up with tblCountries, tblCountries_1, tblCountries_2. This isn't actually duplicating the table. It is simply a visual device to clarify the joins. Now you can create three separate joins. You should Alias the Country name fields as you select them so you end up with ProviderCountry, ToCountry, FromCountry.

PS - it is less confusing if you use the PK name as the FK name rather than the name of the text field. Someone might think your design is incorrect and that they should be joining on Country to Country rather than Country to CountryID. In the table where you have two references to the same PK, then I would use - ToCountryID and FromCountryID. Of course the confusion might be caused because you have used table level lookups but we won't go there now.