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
;
You are getting the error
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 theUNION ALL
has nothing to work with. So, for example, this won't work (resulting in the above error message):However, you can reorganize that query as follows, which does work: