create join between 3 tables, query table 1 results table 3

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 tables that are joined by the field [addnr]. I want to query table
one and get a result from table 3 by going thru table 2. The problem is that
all 3 tables have the [addnr] field but values in table 1 are not found in
table 3, only in table 1 & 2. And table 2 & 3 are joined by using different
values. It's like this:

table1 table2
table3
device | addnr addnr | housenum| street|strtype addnr |
partner
84359 | 12345 <-> 12345 | 11001 | Main | St
98765 | 11001 | Main | St <->
98765 | 90045321

I figured maybe I could use [housenum] & [street] in table 2 because they
occur mulitple times to get to table 3? So I want to query device in table 1
and find out the partner in table 3. Any suggestion welcome!
 
Use an Outer Join from T1 -> T2 -> T3

Right click on Join line, and choose Join Properties.
Pick the option (2 or 3) that makes it look like that (meaning the arrows
pointing in that same direction)
 
Wouldn't an outer join only work where the same number is in T1, T2, & T3?
My problem is that number X only links T1 -> T2. Then there's number Y that
links T2 -> T3. The only way I can see getting from T1 to T3 is by using the
multiple identical addresses (housenum & street) residing in T2? These
addresses seems to be the only common thing linking T1 & T3 to T2.

S.Clark said:
Use an Outer Join from T1 -> T2 -> T3

Right click on Join line, and choose Join Properties.
Pick the option (2 or 3) that makes it look like that (meaning the arrows
pointing in that same direction)

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



simon said:
I have 3 tables that are joined by the field [addnr]. I want to query table
one and get a result from table 3 by going thru table 2. The problem is that
all 3 tables have the [addnr] field but values in table 1 are not found in
table 3, only in table 1 & 2. And table 2 & 3 are joined by using different
values. It's like this:

table1 table2
table3
device | addnr addnr | housenum| street|strtype addnr |
partner
84359 | 12345 <-> 12345 | 11001 | Main | St
98765 | 11001 | Main | St <->
98765 | 90045321

I figured maybe I could use [housenum] & [street] in table 2 because they
occur mulitple times to get to table 3? So I want to query device in table 1
and find out the partner in table 3. Any suggestion welcome!
Click to expand...
Click to expand...
 
Back
Top