CODEWITHSUNDEEP
ms-accessnested
zaptask
zaptask

Reputation: 707

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

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?

Upvotes: 5

Views: 9875

Answers (3)

user28839663
user28839663

Reputation: 1

To get a list of non-related values, to populate a dropdown for instance, you need unions of (SELECT [the values] + FROM [any table]) like in the following example:

SELECT * FROM (SELECT FORMAT(DateAdd("m",-8,Date()),"yyyy_mm") AS periode FROM MSysObjects) 
UNION (SELECT FORMAT(DateAdd("m",-7,Date()),"yyyy_mm") AS periode FROM MSysObjects) 
UNION (SELECT FORMAT(DateAdd("m",-6,Date()),"yyyy_mm") AS periode FROM MSysObjects) 
UNION (SELECT FORMAT(DateAdd("m",-5,Date()),"yyyy_mm") AS periode FROM MSysObjects) 
UNION (SELECT FORMAT(DateAdd("m",-4,Date()),"yyyy_mm") AS periode FROM MSysObjects) 
UNION (SELECT FORMAT(DateAdd("m",-3,Date()),"yyyy_mm") AS periode FROM MSysObjects) 
UNION (SELECT FORMAT(DateAdd("m",-2,Date()),"yyyy_mm") AS periode FROM MSysObjects) 
UNION (SELECT FORMAT(DateAdd("m",-1,Date()),"yyyy_mm") AS periode FROM MSysObjects) 
UNION (SELECT FORMAT(DateAdd("m",0,Date()),"yyyy_mm") AS periode FROM MSysObjects) 
UNION (SELECT FORMAT(DateAdd("m",1,Date()),"yyyy_mm") AS periode FROM MSysObjects) 
UNION (SELECT FORMAT(DateAdd("m",2,Date()),"yyyy_mm") AS periode FROM MSysObjects) 
UNION (SELECT FORMAT(DateAdd("m",3,Date()),"yyyy_mm") AS periode FROM MSysObjects) 
UNION (SELECT FORMAT(DateAdd("m",4,Date()),"yyyy_mm") AS periode FROM MSysObjects)
UNION (SELECT FORMAT(DateAdd("m",5,Date()),"yyyy_mm") AS periode FROM MSysObjects)
UNION (SELECT FORMAT(DateAdd("m",6,Date()),"yyyy_mm") AS periode FROM MSysObjects)
UNION (SELECT FORMAT(DateAdd("m",7,Date()),"yyyy_mm") AS periode FROM MSysObjects)
UNION (SELECT FORMAT(DateAdd("m",8,Date()),"yyyy_mm") AS periode FROM MSysObjects)

Here the values are formatted months from 8 months in the past to 8 months in the future. Sadly this is the most elegant way to do this.

Upvotes: 0

AdamsTips
AdamsTips

Reputation: 1776

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.

Upvotes: 2

HansUp
HansUp

Reputation: 97131

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).

Upvotes: 9

Related Questions