Joining 3 tables into one (Union Query?)

181 views
Skip to first unread message

ielmrani

unread,
Apr 26, 2006, 11:00:34 PM4/26/06
to
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

unread,
Apr 26, 2006, 11:07:45 PM4/26/06
to
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

unread,
Apr 27, 2006, 2:08:47 AM4/27/06
to
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

unread,
Apr 27, 2006, 2:21:01 AM4/27/06
to
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

unread,
Apr 27, 2006, 10:03:41 AM4/27/06
to
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

unread,
Apr 28, 2006, 2:34:01 AM4/28/06
to
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

ielmrani via AccessMonster.com

unread,
Apr 28, 2006, 9:27:54 AM4/28/06
to
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

--

Reply all
Reply to author
Forward
0 new messages