Joining 3 tables into one (Union Query?)

[ielmrani]

Hi all,
I have 3 tables that I would like to combine into one.

Table1 contains the following: Table2
Table3

ProvID ProvName MembName ProvID
MembName ProvID
1111 Smith, Alan John, Ish
1111 Stanco, Al 1234
Bob, Kar
1111 Fisher, Dale 1111

Robinson, Paul 1111
what I am trying to do is create one query to give me the following:

ProvID ProvName MembName
1111 Smith, Alan John, Ish
Bob, Kar
Staco, Al
Robinson, Paul

Thanks in advance

Ismail

 

[ielmrani]

sorry the information looks a mess, here it is again:
Hi all,
I have 3 tables that I would like to combine into one.

Table1 contains the following:


ProvID ProvName
1111 Smith, Alan

Table2 contains the following:

MembName ProvID
John, Ish 1111
Stanco, Al 1111

Table3 contains the following:

MembName ProvID
Bob, Kar 1234
Fisher, Dale 1111
Robinson, Paul 1111


what I am trying to do is create one query to give me the following:

ProvID ProvName MembName
1111 Smith, Alan John, Ish
Bob, Kar
Staco, Al
Robinson, Paul

Thanks in advance

Ismail

 

[Saran]

Ismail,

May be this what you want

Select T1.ProvID, T1.ProvName,T23.MembName From Table1 T1,
(Select * from Table2
Union All
Select * from Table3) T23 Where T1.ProvID = T23.ProvID

Thanks,
Saran.

 

[Guest]

Hi Ismail,

First, create the following Union query. Save it as Query1:

SELECT Table2.ProvID, Table2.MembName
FROM Table2
UNION SELECT Table3.ProvID, Table3.MembName
FROM Table3
ORDER BY MembName DESC;


Then create the following query:

SELECT Table1.ProvID, Table1.ProvName, Query1.MembName
FROM Table1
RIGHT JOIN Query1 ON Table1.ProvID = Query1.ProvID
WHERE (((Query1.MembName)<>"Fisher, Dale"));


This will return a result that looks like this. You can use a report, with
the hide duplicates property set, to supress the printing of 1111 and Smith,
Alan multiple times. However, you cannot suppress this in the query:

ProvID ProvName MembName
1111 Smith, Alan John, Ish
1111 Smith, Alan Robinson, Paul
1111 Smith, Alan Stanco, Al
Bob, Kar

I wonder if you did not mean to include Fisher, Dale in place of Bob, Kar.
If this is the case, change the SQL statement for the second query to this:

SELECT Table1.ProvID, Table1.ProvName, Query1.MembName
FROM Table1 INNER JOIN Query1 ON Table1.ProvID = Query1.ProvID;


Good Luck,

Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[ielmrani via AccessMonster.com]

Great. You guys are the best. This is the first time I use this site and
it's amazing.
For some reason I could not make Saran's way work but Tom's way worked fine.
Again thank you very much for you help

Ismail

Hi Ismail,

First, create the following Union query. Save it as Query1:

SELECT Table2.ProvID, Table2.MembName
FROM Table2
UNION SELECT Table3.ProvID, Table3.MembName
FROM Table3
ORDER BY MembName DESC;

Then create the following query:

SELECT Table1.ProvID, Table1.ProvName, Query1.MembName
FROM Table1
RIGHT JOIN Query1 ON Table1.ProvID = Query1.ProvID
WHERE (((Query1.MembName)<>"Fisher, Dale"));

This will return a result that looks like this. You can use a report, with
the hide duplicates property set, to supress the printing of 1111 and Smith,
Alan multiple times. However, you cannot suppress this in the query:

ProvID ProvName MembName
1111 Smith, Alan John, Ish
1111 Smith, Alan Robinson, Paul
1111 Smith, Alan Stanco, Al
Bob, Kar

I wonder if you did not mean to include Fisher, Dale in place of Bob, Kar.
If this is the case, change the SQL statement for the second query to this:

SELECT Table1.ProvID, Table1.ProvName, Query1.MembName
FROM Table1 INNER JOIN Query1 ON Table1.ProvID = Query1.ProvID;

Good Luck,

Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

sorry the information looks a mess, here it is again:
Hi all,

[quoted text clipped - 29 lines]

Ismail

 

[Guest]

Hi Ielmrani,

Two words: Name Autocorrupt

I really liked Saran's single query solution but, like you, I had trouble
getting it to work consistently. Then I disabled the default Name Autocorrect
(Tools > Options... | General Tab. After doing that, the query appears to
work very consistantly. The Name Autocorrupt option was changing the SQL
statement to this, which does not work:

SELECT T1.ProvID, T1.ProvName, T23.MembName
FROM Table1 AS T1, [Select * from Table2 Union All Select * from Table3]
AS T23
WHERE T1.ProvID=T23.ProvID;


Note: This change is *not* getting logged if one enables the option that
reads "Log name Autocorrupt changes"! Just one more reason to always avoid
using this default option.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[ielmrani via AccessMonster.com]

Thank you very Tom for the update. I will try you suggestion I rather make
one query instead of two.

Hi Ielmrani,

Two words: Name Autocorrupt

I really liked Saran's single query solution but, like you, I had trouble
getting it to work consistently. Then I disabled the default Name Autocorrect
(Tools > Options... | General Tab. After doing that, the query appears to
work very consistantly. The Name Autocorrupt option was changing the SQL
statement to this, which does not work:

SELECT T1.ProvID, T1.ProvName, T23.MembName
FROM Table1 AS T1, [Select * from Table2 Union All Select * from Table3]
AS T23
WHERE T1.ProvID=T23.ProvID;

Note: This change is *not* getting logged if one enables the option that
reads "Log name Autocorrupt changes"! Just one more reason to always avoid
using this default option.

Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Great. You guys are the best. This is the first time I use this site and
it's amazing.
For some reason I could not make Saran's way work but Tom's way worked fine.
Again thank you very much for you help

Ismail