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.

[Tom Wickerath]

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

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

>[quoted text clipped - 29 lines]
>>
>> Ismail

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200604/1

[Tom Wickerath]

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.

Tom Wickerath wrote:
>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

--