"Query input must contain at least one table or query" error - nested queries, MS Access

Question

Essentially we are sometimes (?) required to provide a reference to table even if I do not need it. E.g. Query input must contain atleast one table or query

The question I have is why query q1 SELECT 1 executes just fine and gives me 1 row-1 column resultant table with 1 as the value but query q2 SELECT * FROM q1 produces the aforementioned error?

When I change q1 to SELECT 1 from dummy_table where dummy_table is a dummy table with dummy value, q2 runs fine.

Why q1's internal structure is in any way relevant to q2? q1 on its own works just fine. Does the q2 "unrolls" q1 and then compiles a statement SELECT * FROM (SELECT 1) (which on its own produces the same error). Can I somehow force Access not to peek into parents' internal structure? Also why SELECT * FROM (SELECT 1) gives an error and SELECT 1 works fine?

Elvera

Answer 1

Access will only accept a query without a FROM clause when the "naked" SELECT is used in isolation, not as part of another query.

As you discovered, SELECT 1 is valid when it is the entire statement. But Access complains "Query input must contain at least one table or query" if you attempt to use that "naked" SELECT in another query such as SELECT q.* FROM (SELECT 1) AS q;

Similarly, although SELECT 1 and SELECT 2 are both valid when used alone, attempting to UNION them triggers the same error:

SELECT 1
UNION ALL
SELECT 2

There is no way to circumvent that error. As you also discovered, saving the "naked" SELECT as a named query, and then using the named query in another still triggers the error. It's just a limitation of the Access db engine, and it's been that way with every Access version I've used (>= Access 2000).

Nutriment

Thank you for the answer. But why? What is the reason for it? It allows for some execution time optimization? – Elvera

I don't really know the answer to why. I suspect support for a naked SELECT used alone was relatively easy to implement. But supporting a naked SELECT as part of another query required too much additional development effort for the query parser design, and MS decided it wasn't worth the effort. But that's only speculation. You'll probably need to ask MS. – Nutriment

Answer 2

We ran into this issue today with error 3067 when trying to add some records to query results using a UNION query.

This doesn't work:

SELECT 
UserID, UserName
FROM USERS
UNION SELECT
0, 'Add User...'

But as pointed out in the original question, if you use a valid table name, you can work around the issue.

Simply adjust the code to select a single record (TOP 1) from any table. Here I use MSysObjects because that should always exist and have records.

SELECT 
UserID, UserName
FROM USERS
UNION SELECT TOP 1
0, 'Add User...'
FROM MSysObjects

Even though we are technically not using any data from the "dummy" table, it satisfies the compiler requirements for our union query and returns the desired results.

Quorum