How to specify a specific join in a query?

[hans37]

How to specify a specific join in a query?

I have three tables: members, membersupdate, countries. The tables members and membersupdate are have the same fields and are connecter throug an inner join on membersid.
Both tables have a field which contains a code for the country members live in. There is code table called countries which contains the code and the countryname. Both table (members and membersupdate) are connected with the table countries through an inner join on countrycode. So there are three joins.

Now I want to make a query which contains the three tables that give a calculated result like:

member & “used tot live in “ & countryname & “but now in “ & countryname. The first field countryname should give the result of the join with members and the second of the join with membersupdate.

How can I realize this?

 

[AlexMidd]

You should do three queries.

Query1: Members table joined to Countries table

Query2: Membersupdate table joined to Countries table

Ensure that these work and give the correct results before proceeding.

Query3: Query1 joined to Query2. You may have to fiddle with the join properties for this last query but I would assume that all records should be chosen from each table where you primary keys match (eg memberid). Have fun! )

Alex Middleton

 

[tempclerk]

If you are doing this in the query grid, you can add the Countries table twice; it will show up as Countries and Countries_1. Join Countries to Members and Countries_1 to Membersupdate, then your expression would be something like
member & “used tot live in “ & Countries.countryname & “but now in “ &
Countries_1.countryname

 

[AlexMidd]

That's another way of doing it, but the developer in me tells me that it is wasteful and it is best avoided. Have fun! )

Alex Middleton

 

[tlbroadbent]

Alex,

What do you think is wasteful and best avoided? If you are responding to tempclerk's post about including the Countries table twice, then I disagree. It is essentially equivalent to the three queries you proposed earlier. I prefer your solution for readability, maintainability, etc. but performance should be nearly equivalent.

The SQL code would look like this.

Select m1.membername &
" used to live in " &
c1.country &
" but now lives in " &
c2.country

From (members m1
Inner Join Countries On m1.CountryCode=c1.Code)
Inner Join (membersupdate m2
Inner Join Countries On m2.CountryCode=c2.Code)
On m1.memberid=m2memberid

--------------------------
NOTES:

Use table aliases (m1, c1, etc.) to shorten the code and improve readability.

I modified the query structure because I don't like the way Access structures the SQL after using the designer grid to create the query. This step isn't required. It is just a personal preference. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous