Trying to create a new table based on the results of 2 joined queries

[cirugio]

Trying to create a new Access table based on the results of 2 join queries (see below). Each query individually runs fine with no errors, but when I use UNION I receive an error message "An action query cannot be used as a row source". Can someone help? Thank you!

CODE:





SELECT file1.Lastname INTO Combined_Results
FROM TPX LEFT JOIN file1 ON TPX.[SECURITY ID] = file1.CUSIP
WHERE (((file1.cusip) Is Not Null));


union



SELECT file2.Lastname INTO Combined_Results
FROM SMX LEFT JOIN file2 ON SMX.[SECURITY ID] = file2.CUSIP
WHERE (((file2.cusip) Is Not Null));

 

[pella88]

Re: Access: Trying to create a new table based on the results of 2 joined queries

Hi cirugio,

Your query is giving error due to the fact that you are writing INTO same table twice, and at the same time using union. If you are writing INTO same table, there should be no reason to use union, however, if you want to combine results of two queries, you need to union then before inputing into table. The easiest way would be:

SELECT 
    * 
INTO 
    Combined_Results
FROM 
         (
[COLOR=#333333]           SELECT file1.Lastname[/COLOR]
[COLOR=#333333]           FROM TPX LEFT JOIN file1 ON TPX.[SECURITY ID] = file1.CUSIP[/COLOR]
[COLOR=#333333]           WHERE (((file1.cusip) Is Not Null))[/COLOR]

[COLOR=#333333]           UNION
[/COLOR]
[COLOR=#333333]           SELECT file2.Lastname[/COLOR]
[COLOR=#333333]           FROM SMX LEFT JOIN file2 ON SMX.[SECURITY ID] = file2.CUSIP[/COLOR]
[COLOR=#333333]           WHERE (((file2.cusip) Is Not Null))
[/COLOR]          )

This code will select the requested data from tables SMX and TPX with the requested conditions, union them and then write them into Combined_Results.

Br
pella88

 

[Joe4]

Re: Access: Trying to create a new table based on the results of 2 joined queries

Cross-posted: https://www.accessforums.net/showthread.php?t=78359

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[cirugio]

Re: Access: Trying to create a new table based on the results of 2 joined queries

Thank you Pella88. This worked :0) when I run it as a saved query in Access.
Now I want it to take it a step future and add logic with other sql logic in Module (see below).
When I run the module now a strange error that states "Run-time error '3067': Query input must contain at least one table or query."

Not sure why I am getting this error, because the Combined_Results table exists. Any thoughts?

Below is the code with additional syntax used for running it using DoCumd.RunSql:

MODULE CODE:
-------------------
DoCmd.RunSQL "SELECT * INTO Combined_Results" & _
"FROM (SELECT file1.Lastname FROM TPX LEFT JOIN file1 ON TPX.[SECURITY ID] = file1.CUSIP " & _
"WHERE (((file1.cusip) Is Not Null))" & _
"UNION" & _
"SELECT file2.Lastname FROM SMX LEFT JOIN file2 ON SMX.[SECURITY ID] = file2.CUSIP" & _
"WHERE (((file2.cusip) Is Not Null)) );"

 

[Joe4]

Re: Access: Trying to create a new table based on the results of 2 joined queries

Try adding spaces at the end of all your lines (before the last double-quote), or else the end of line runs unto the next, i.e.

[COLOR=#333333]DoCmd.RunSQL "SELECT * INTO Combined_Results " & _[/COLOR]
[COLOR=#333333]"FROM ([/COLOR][COLOR=#333333][COLOR=#333333]SELECT file1.Lastname [/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]FROM TPX LEFT JOIN file1 ON TPX.[SECURITY ID] = file1.CUSIP[/COLOR][/COLOR][COLOR=#333333] " & _[/COLOR]
[COLOR=#333333]"[/COLOR][COLOR=#333333][COLOR=#333333]WHERE (((file1.cusip) Is Not Null)) [/COLOR][/COLOR][COLOR=#333333]" & _[/COLOR]
[COLOR=#333333]"[/COLOR][COLOR=#333333][COLOR=#333333]UNION " & _[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]"SELECT file2.Lastname [/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]FROM SMX LEFT JOIN file2 ON SMX.[SECURITY ID] = file2.CUSIP [/COLOR][/COLOR][COLOR=#333333]" & _[/COLOR]
[COLOR=#333333][COLOR=#333333]"WHERE (((file2.cusip) Is Not Null))[/COLOR][/COLOR][COLOR=#333333]);"[/COLOR]