How to insert results of 2 join queries into 1 table

[ciru]

Trying to create a new table based on the results of 2 joined 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 BRAM_Cusips.BRAM_MARKET_METHOD_PERCENT INTO Combined_Results
FROM TPX LEFT JOIN BRAM_Cusips ON TPX.[SECURITY ID] = BRAM_Cusips.CUSIP
WHERE (((BRAM_Cusips.cusip) Is Not Null));


union

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

[JoeM]

I don't think you can do a UNION between two Append Queries.

I would recommend doing it in a two-step queries process, either:
1. Do a Union Query between two SELECT queries, and then create an Insert Query from that Union Query.
- or -
2. Run each Append Query individually
(no Union Query needed, unless you are trying to avoid duplicates between the two, in which case go with option 1)

[ArviLaanemets]

Try

SELECT uq.* INTO Combined_Result
FROM
(SELECT BRAM_Cusips.BRAM_MARKET_METHOD_PERCENT
 FROM TPX LEFT JOIN BRAM_Cusips ON TPX.[SECURITY ID] = BRAM_Cusips.CUSIP
 WHERE (((BRAM_Cusips.cusip) Is Not Null))
UNION
 SELECT BRAM_ISINs.BRAM_MARKET_METHOD_PERCENT
 FROM SMX LEFT JOIN BRAM_ISINs ON SMX.[SECURITY ID] = BRAM_ISINs.CUSIP
 WHERE (((BRAM_ISINs.cusip) Is Not Null))) AS uq;

Or create table Combined_Results, and then try query

ISERT INTO Combined_Result
SELECT uq.*
FROM
(SELECT BRAM_Cusips.BRAM_MARKET_METHOD_PERCENT
 FROM TPX LEFT JOIN BRAM_Cusips ON TPX.[SECURITY ID] = BRAM_Cusips.CUSIP
 WHERE (((BRAM_Cusips.cusip) Is Not Null))
UNION
 SELECT BRAM_ISINs.BRAM_MARKET_METHOD_PERCENT
 FROM SMX LEFT JOIN BRAM_ISINs ON SMX.[SECURITY ID] = BRAM_ISINs.CUSIP
 WHERE (((BRAM_ISINs.cusip) Is Not Null))) AS uq;

[ciru]

Try

SELECT uq.* INTO Combined_Result
FROM
(SELECT BRAM_Cusips.BRAM_MARKET_METHOD_PERCENT
 FROM TPX LEFT JOIN BRAM_Cusips ON TPX.[SECURITY ID] = BRAM_Cusips.CUSIP
 WHERE (((BRAM_Cusips.cusip) Is Not Null))
UNION
 SELECT BRAM_ISINs.BRAM_MARKET_METHOD_PERCENT
 FROM SMX LEFT JOIN BRAM_ISINs ON SMX.[SECURITY ID] = BRAM_ISINs.CUSIP
 WHERE (((BRAM_ISINs.cusip) Is Not Null))) AS uq;

Or create table Combined_Results, and then try query

ISERT INTO Combined_Result
SELECT uq.*
FROM
(SELECT BRAM_Cusips.BRAM_MARKET_METHOD_PERCENT
 FROM TPX LEFT JOIN BRAM_Cusips ON TPX.[SECURITY ID] = BRAM_Cusips.CUSIP
 WHERE (((BRAM_Cusips.cusip) Is Not Null))
UNION
 SELECT BRAM_ISINs.BRAM_MARKET_METHOD_PERCENT
 FROM SMX LEFT JOIN BRAM_ISINs ON SMX.[SECURITY ID] = BRAM_ISINs.CUSIP
 WHERE (((BRAM_ISINs.cusip) Is Not Null))) AS uq;

That worked perfectly. Thank you!