Table-less UNION query in MS Access (Jet/ACE)

Question

This works as expected:

SELECT "Mike" AS FName

This fails with the error "Query input must contain at least one table or query":

SELECT "Mike" AS FName
UNION ALL
SELECT "John" AS FName

Is this just a quirk/limitation of the Jet/ACE database engine or am I missing something?

Bazar

That is also what I have found, but you can select from any one line table as a work around, or select distinct from any table. – Watchband

Yeah, I keep a local Dummy table with 1 field and 1 record and use it like you suggest. It's just something that's always mildly annoyed me and I wanted to make sure I wasn't missing something simple. – Bazar

I've never personally seen any documentation even on MSDN regarding this, as you have already mentioned to get around it use an existing table name. – Saied

The MS Access reference for SELECT does not present the FROMas optional. So I guess that this is interpreted somewhere else, where you don't have full SQL. Which seems consistent with this question on SO where Access requires a FROM if you put subquery in the mix. .. I haven't tested all these, of course. – Crary

Answer 1

You didn't overlook anything. Access' database engine will allow a single row SELECT without a FROM data source. But if you want to UNION or UNION ALL multiple rows, you must include a FROM ... even if you're not referencing any field from that data source.

I created a table with one row and added a check constraint to guarantee it will always have one and only one row.

Public Sub CreateDualTable()
    Dim strSql As String
    strSql = "CREATE TABLE Dual (id COUNTER CONSTRAINT pkey PRIMARY KEY);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
    strSql = "INSERT INTO Dual (id) VALUES (1);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql

    strSql = "ALTER TABLE Dual" & vbNewLine & _
        vbTab & "ADD CONSTRAINT there_can_be_only_one" & vbNewLine & _
        vbTab & "CHECK (" & vbNewLine & _
        vbTab & vbTab & "(SELECT Count(*) FROM Dual) = 1" & vbNewLine & _
        vbTab & vbTab & ");"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
End Sub

That Dual table is useful for queries such as this:

SELECT "foo" AS my_text
FROM Dual
UNION ALL
SELECT "bar"
FROM Dual;

Another approach I've seen is to use a SELECT statement with TOP 1 or a WHERE clause which restricts the result set to a single row.

Note check constraints were added with Jet 4 and are only available for statements executed from ADO. CurrentProject.Connection.Execute strSql works because CurrentProject.Connection is an ADO object. If you try to execute the same statement with DAO (ie CurrentDb.Execute or from the Access query designer), you will get a syntax error because DAO can't create check constraints.

Racoon

I'd love to know the reason for the design or see some documentation on the behavior, but enough experienced people have weighed in that I feel confident that this is the answer. – Bazar

I haven't seen it documented anywhere. – Racoon

I keep a Dummy table which is essentially the same as the Dual table you show here. Your check constraint is an improvement over what I have. Whenever I write a query I'm always wary that some day I'll inadvertently add rows to the Dummy table. I end up taking the extra cautious approach of adding the WHERE clause or TOP 1 (as you also suggest). Adding the check constraint would give me the confidence to improve the efficiency of those queries (ever so slightly) by dropping the WHERE/TOP. – Bazar

Thank you. @onedaywhen showed me how to use ADO for CHECK CONSTRAINT; you can't do it with DAO. I appreciate that Dual allows me to write concise single-row queries ... even though I don't actually do it often. – Racoon

Thanks for pointing out the ADO reqt. I missed that the first time reading it. I assume as you've written your code there's no need to actually add ADO as a reference. Simply using the Execute method off of the CurrentProject.Connection is enough, right? – Bazar

Right, CurrentProject.Connection is an ADO object and is available without setting a reference for ADO ... I think starting with Access 2000. (I'm not positive about when.) – Racoon

+1 for "rolling your own" DUAL. I've done something similar in the past, but I've just made [ID] the Primary Key and set a field constraint specifying that ID must have the value 1. (I was using DAO TableDef and related objects to create the table.) – Bellbella

@GordThompson Please correct me if I have this point wrong: Your design would permit no more than one row, but would also allow that one row to be deleted. I wanted to guarantee Dual always contains exactly one row. – Racoon

No, you're right, there's nothing preventing that row in my table from being deleted. I'll keep your method in mind the next time something like this comes up! – Bellbella

You can actually do without an additional physical table: https://mcmap.net/q/483839/-table-less-union-query-in-ms-access-jet-ace. But I feel that this is really a bit quirky – Quinque

I had a working FROM-less query, but run into the same error when using it as record source for a Report. – Gong

"check constraints...are only available for statements executed from ADO" -- a misstatement. Although the Access query designer defaults to ANSI-89 Query Mode, which indeed causes the CHECK syntax to fail, you can change the Access query designer to ANSI-92 Query Mode, allowing the syntax to succeed. DAO always uses ANSI-89 Query Mode and ADO always uses ANSI-92 Query Mode. But the designer can use either. – Flattish

the problem with select top 1 is that:if table 1 is empty then union returns nothing for the second statement – Necolenecro

Answer 2

If you have access to some system tables, you can emulate a dual table this way:

(SELECT COUNT(*) FROM MSysResources) AS DUAL

Unfortunately, I'm not aware of any system tables that...

• are always available, readable (MSysObjects might not be accessible to every connection)
• contain exactly one record, such as Oracle's DUAL or DB2's SYSIBM.DUAL

So you'd write:

SELECT 'Mike' AS FName
FROM (SELECT COUNT(*) FROM MSysResources) AS DUAL
UNION ALL
SELECT 'John' AS FName
FROM (SELECT COUNT(*) FROM MSysResources) AS DUAL

This is what is being implemented as a syntactic element in jOOQ, for instance.

Quinque

i like this idea. if you know the name of any available system table, you could do something like FROM (SELECT TOP 1 NULL FROM MSysResources) AS DUAL .. the TOP 1 limits to 1 row, and the NULL is the single value returned - it removes the need to know any column name. You need just a table name. – Surfboard

That could work mostly, but from experience, there's always a very subtle caveat in all RDBMS when using TOP in some specific case of subquery... – Quinque

MSysResources is not present in any of the MDB databases I'm working with. I do have MSysObjects but I get permissions errors when using them in queries over OLE-DB. Grumble. – Biparous

@Dai: you can use any other table – Quinque

Answer 3

When you have restricted read-only access to the database (i.e you cannot create new tables or access system resources), this could work:

SELECT "Mike" AS FName
FROM (SELECT COUNT(*) FROM anyTable WHERE 1=0) AS dual

1. anyTable is the first user table you find (I can hardly imagine a real-life database with no user table!).

2. WHERE 1=0 is supposed to return fast a count of 0, even on a large table (hopefully the Jet engine is smart enough to recognize such trivial condition).

Campbellbannerman

I used your method and created a query named SingleRowQuery: SELECT Count(*) FROM UserTable WHERE 1=0; Then I used it in the union query: SELECT CustNo, CustName FROM Customers WHERE CustNo = 1 UNION SELECT 0, '(NA)' FROM SingleRowQueryORDER BY CustName – Preeminence

I like this approach best because it avoids using MSysObjects (which often has permissions issues preventing its use). My own version which matches Oracle's DUAL is this: ( SELECT TOP 1 IIF( COUNT(*) = 0, 'X', 'X' ) AS DUMMY FROM [anyTable] WHERE 1 = 0 ) AS [dual] – Biparous

Answer 4

Here's a much simpler way to do it:

SELECT 'foo', 'boo', 'hoo' from TableWith1Row
union
SELECT 'foo1', 'boo1', 'hoo1' from TableWith1Row

Important: TableWith1Row can EITHER be a table with literally 1 record (which you ignore anyway) OR it can be a table with any number of rows (must have AT LEAST 1 row) but you add a WHERE clause to ensure 1 row. This is a bit loosy-goosy but it's a quick way to make this work without creating more tables.

Coplin

Answer 5

If someone wants to use the Top 1 method, it would look like this:

SELECT first_name AS FName
FROM tblname
UNION ALL
SELECT "Mike" as Fname
FROM (Select Top 1 Count(*) FROM tblsometable);

The alias for the field has to be the same on both sides of the union, in this case "FName".

Tusk

Why Select Top 1 Count(*)? Without a GROUP BY clause, Select Count(*) can only return one row. What is the advantage of including TOP 1 there? – Racoon

Answer 6

Put in any table name (you don't need to actually select a column from it).

This query gives me the 3 fiscal years I need for a dropdown. The fiscal year begins in July.

SELECT IIf(Month(Now())>6,Year(Now())-1,Year(Now())-2) AS FY
FROM table
UNION 
SELECT IIf(Month(Now())>6,Year(Now()),Year(Now())-1) AS FY
FROM table
UNION 
SELECT IIf(Month(Now())>6,Year(Now())+1,Year(Now())) AS FY
FROM table;

Kingofarms

Answer 7

Here is a query that actually works in access. I tested it. It uses a sub-query as "dual" : (SELECT count(*) as cnt from MSysObjects).

select 1 as a from (SELECT count(*) as cnt from MSysObjects) UNION ALL
select 2 as a from (SELECT count(*) as cnt from MSysObjects) UNION ALL
select 3 as a from (SELECT count(*) as cnt from MSysObjects)

Tombac

Please add some explanation to your answer such that others can learn from it. – Laban