Table-Less Union Query in Ms Access (Jet/Ace)

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

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.

Union Query - Pick One When Duplicate Value in Jet

How about

 SELECT Id, Max(Field) 
 FROM ( Select Id, Field FROM John
        Union All ...)
 GROUP BY Id

How to order a UNION query that has a select all function

One thing that you can is add a field that you can use to sort the data:

Select "*" as AllRec,  "<>" as Allrecords, 1 as SortOrder
FROM Treatment 
UNION 
Select Treatment.TreatmentID, Treatment.Treatment, 2 as SortOrder
From Treatment
ORDER BY SortOrder, AllRec;

The ORDER BY is always applied last so it will not know the Treatment.TreatmentID column name.

Query input must contain atleast one table or query

This is one of those cases where a Dual table can be helpful. A Dual table is a single-row table that can be used in the FROM clause of a query when you don't really need a source table but the SQL parser insists on there being one.

Some database systems (e.g., Oracle) provide a Dual virtual table as "standard equipment", but in Access we need to create our own. For an excellent description of the process check out HansUp's answer here.

So, once you have your [Dual] table in place, i.e.,

id
--
 1

...then you can use this query do perform your INSERT (or not...):

INSERT INTO FinalizedPrintedStickers
    (
        SN,
        Field2
    )
    SELECT 
        "0000846043-481-9-0" AS SN,
        "48IG - 1F Straight Panel" AS Field2
    FROM Dual
    WHERE DCount("SN","FinalizedPrintedStickers","SN=""0000846043-481-9-0""")=0

using InStrRev() and similar functions in Jet/ACE queries outside of Access

As you have discovered, SQL queries executed from within Access can use many VBA functions (like InStr() and InStrRev()) that are not natively supported by the Jet/ACE dialect of SQL. One of the most powerful aspects of Access' "extensions" to Jet/ACE SQL is that you can even write your own VBA functions and use them in Access queries.

Unfortunately, things can get a bit confusing when it comes to what functions are supported in "plain" Jet/ACE SQL because many of the names are the same. If we can use Left(), Right(), Mid(), etc. in queries against Jet/ACE databases executed outside of Access, then why not InStr() and InstrRev()? The answer is, unfortunately, "Just because.".

In your particular case I agree with Remou's comment that you'll probably just have to retrieve the entire column value in your query and then do the [equivalent of the] InStrRev()-related parsing afterward.

Access SELECT Statement and Hard coded Value

You can use a UNION query which adds a single row of fabricated values to what you have now.

SELECT a.PatchID, a.OS
FROM tbl_Patches_Cisco_SAs_Applicability AS a
WHERE a.PatchID=[Forms]![Patch_Management_Cisco_Stage2]![Publication_ID]
UNION ALL
SELECT 0 AS PatchID, 'NA' AS OS
FROM Dual;

Dual can be any table or query which returns only one row. I use a custom table for that purpose: CreateDualTable()

---

Related Topics