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

Question

I'm attempting to turn an already working UNION query into a SELECT INTO as part of a VBA procedure for a bunch of temp tables. I'm doing this because Access is having performance issues.

When I try to run this query with the INTO clauses inserted, I get this error:

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

I'm not entirely sure why I can't execute it or how to get around it without creating more queries. What is causing this and how do I 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

;

@HansUp I'm not entirely sure I know what you're asking but the base data that the queries come from do not contain any fields with combo boxes or list boxes. — plankton, Jun 16 '15 at 14:06
Oops! Now I see. You have 2 `SELECT INTO` statements which you're attempting to combine with a `UNION`. No dice! Make both plain `SELECT` statements and use `INTO` with the result set of the top-level `SELECT` I'm not positive Access will accept even that, but try it first. — HansUp, Jun 16 '15 at 14:12
@HansUp It came up with the same error when placing the INTO in front of the first FROM clause :( — plankton, Jun 16 '15 at 14:21
i'm not sure what you expect. a `select into` is something without any output, so what exactly should access do with the union? — A ツ, Jun 16 '15 at 15:12
@Aツ Can you elaborate more? I'd like to just keep the query as it is now instead of breaking it up into two different queries just to `SELECT INTO` then `INSERT INTO` where I could do it in the `UNION` of the two. Maybe I'm not getting something with regards to the functionality. — plankton, Jun 16 '15 at 15:25
`select into` is the basicly the same as `insert into`. there is nothing what could be used to do a uinion, you'll see if you run the query without the second part (and without the union of course). i have really no idea whats the purpose of your query — A ツ, Jun 16 '15 at 15:31

score 5 · Accepted Answer · answered Jun 17 '15 at 09:51

You are getting the error

An action query cannot be used as a row source.

because a SELECT ... INTO NewTableName FROM ... query is an action query (like an INSERT, UPDATE, or DELETE query) which does not return a result set. Therefore the UNION ALL has nothing to work with. So, for example, this won't work (resulting in the above error message):

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

However, you can reorganize that query as follows, which does work:

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

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

1 Answers1