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

cirugio

Board Regular
Joined
Mar 30, 2010
Messages
130
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));
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

pella88

Board Regular
Joined
Aug 14, 2013
Messages
67
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:
Code:
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
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 30, 2010
Messages
130
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)) );"
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
[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]
 

Forum statistics

Threads
1,143,673
Messages
5,720,223
Members
422,270
Latest member
CaptainMurray

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top