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
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
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.
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
__________________________________________
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
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
__________________________________________
--