MS Access SQL: using SELECT INTO with a UNION ALL query

Question

I am trying to turn an existing query UNIONinto SELECT INTOas part of a procedure VBAfor a bunch of temporary tables. I do this because Access has performance issues.

When I try to run this query with added sentences INTO, I get this error:

"An action query cannot be used as a row source."

I am not quite sure why I cannot execute it or how to get around it without creating more queries. What causes this and how to fix it?

SELECT DISTINCT A.*
FROM (SELECT DISTINCT
C.[CUSTOMER CODE], 
D.[FISCAL DAY], 
D.[TY INVOICE DATE] AS [Invoice Date], 
C.[CUSTOMER CODE]&" - "&D.[FISCAL DAY] AS CONCAT, 
IIF(D.[TY INVOICE DATE] Is Not Null, 'TY', Null) As TYLY

***INTO [FD CUST Missing- Temp]***

FROM [QR- CUST FD] AS C,
[QR- FD SALES] AS D) AS A

LEFT JOIN [QR- CUST FD] AS C
ON C.[FD CONCAT] = A.[CONCAT]
WHERE C.[FD CONCAT] Is Null

UNION ALL 

SELECT DISTINCT 
A.*

***INTO [FD Cust Missing- Temp]***

FROM (SELECT DISTINCT
C.[CUSTOMER CODE],
D.[FISCAL DAY],
D.[LY INVOICE DATE] AS [Invoice Date], 
C.[CUSTOMER CODE]&" - "&D.[FISCAL DAY] AS CONCAT, 
IIF(D.[LY INVOICE DATE] Is Not Null, 'LY', Null) As TYLY

FROM [QR- CUST FD] AS C,
[QR- FD SALES] AS D)  AS A

LEFT JOIN [QR- CUST FD] AS C
ON C.[FD CONCAT] = A.[CONCAT]
WHERE C.[FD CONCAT] IS NULL

;

Answer 1

You get an error message

An action request cannot be used as the source of a string.

SELECT ... INTO NewTableName FROM ... - (, INSERT, UPDATE DELETE), . UNION ALL . , , ( ):

    SELECT DISTINCT LastName 
    INTO Foo 
    FROM Clients 
    WHERE LastName LIKE 'D*'
UNION ALL
    SELECT DISTINCT LastName 
    INTO Foo 
    FROM Clients 
    WHERE LastName LIKE 'T*'

:

SELECT *
INTO Foo
FROM
    (
        SELECT DISTINCT LastName 
        FROM Clients 
        WHERE LastName LIKE 'D*'
    UNION ALL
        SELECT DISTINCT LastName 
        FROM Clients 
        WHERE LastName LIKE 'T*'
    )